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]

=SUMIFS(B:B,A:A,D1)

will give you a total (i.e., 23 = 11+12).

 

[JamesWayne]

Thanks Aladin but I don't want to add the numbers together just count the cell.

 

[pgc01]

Hi James

Your example is not clear.

It seems to me you are just counting how many times "Car1" appears in column A which is a simple countif: =COUNTIF(A1:A3,D1)

You say, however, that you don't want to count column A.

Please clarify.

 

[Aladin Akyurek]

Thanks Aladin but I don't want to add the numbers together just count the cell.

If it's not just:

=COUNTIFS(A:A,D1)

perhaps:

=SUMPRODUCT(--($A$2:$A$4=$D1),--ISNUMBER($B$2:$B$4))

 

[JamesWayne]

Apologies for not being clear, let me try again.

Currently I'm using "=COUNTA(B1:B3)" which counts everything if there is a value (not always a number can be letters) if there is nothing in cell it doesn't count it however I need to match D1 to column A1:A3 and if find match then COUNT it, if it's empty then don't count it.

Thank you for baring with me.

 

[Aladin Akyurek]

Apologies for not being clear, let me try again.

Currently I'm using "=COUNTA(B1:B3)" which counts everything if there is a value (not always a number can be letters) if there is nothing in cell it doesn't count it however I need to match D1 to column A1:A3 and if find match then COUNT it, if it's empty then don't count it.

Thank you for baring with me.

=COUNTIFS(A1:A3,D1,B1:B3,"<>")

If this is also not what you want, try to post a more illustrative sample along with the expected count.

 

[JamesWayne]

Thanks Aladin that actually gave me a #VALUE ! error however you also gave me a idea which has actually worked!

=SUMPRODUCT((A1:A3=D1)*(B1:B3<>""))

Thanks for your time and patience!

 

[Aladin Akyurek]

Thanks Aladin that actually gave me a #VALUE ! error however you also gave me a idea which has actually worked!

=SUMPRODUCT((A1:A3=D1)*(B1:B3<>""))

Thanks for your time and patience!

1)

=COUNTIFS(A1:A3,D1,B1:B3,"<>")

2)

=SUMPRODUCT(--(A1:A3=D1),1-(B1:B3=""))

3)

=SUMPRODUCT((A1:A3=D1)*(B1:B3<>""))

are all equivalent. [1] is faster than the other two. [2] complies with the SumProduct specifications better than [3] does.

 

[pgc01]

Hi Aladin

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