COUNT column matching criteria

[JamesWayne]

Hi All, I've been trying to work out a formula that will count a column if the criteria is matched and I've using SUMPRODUCT, COUNTIF functions without much success.

What I'm after is if range A1:A3 matches cell D1 then COUNTA column B is basically what I need to work. I don't want column A to be counted, column A is just the criteria to work out what in column B to count.

Answer below should be 2 (no sum, just count), thank you.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Car 1[/TD]
[TD]11[/TD]
[TD][/TD]
[TD]Car1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Car 2[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Car 1[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Hi Aladin

If I'm not mistaken the first one will only discard empty B cells, null strings in B will be counted.

You are not mistaken. I should have added to [1] and [2/3] are equivalent if the B-range does not house any formulas. Otherwise, [2] would be the formula to use.