Need a count

[Guest]

A B
1 Ford June
2 Ford May
3 BMW June
4 Ford June
5 Ford June
I need to COUNT how many "Fords" have the value of June - the answer should
be 3 in this sample. I tried sumif, countif, sumproduct, maybe I need a
combination?
Thanks.

 

[renegan]

You can use a support column C with this formula:
=IF(AND(A1="Ford",B1="June"),1,0)
Apply this all the rows and get the total of column C.

 

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(A1:A5="Ford"),--(B1:B5="June"))

Biff

 

[Guest]

sum product should work
= sumproduct(--(A1:A5="Ford"),--(B1:B5="June"))
if there is a chance there are leading or trailing spaces try
= sumproduct(--(trim(A1:A5)="Ford"),--(trim(B1:B5)="June"))

 

[Guest]

Hi,

You may also want to try using an array formula although sumproduct is
definitely a better solution

=sum(if(($A$1:$A$5="Ford")*($B$1:$B$5="June"),1,0))

confirmed by a Ctrl+Shift+Enter

Regards,

Ashish Mathur

 

[Biff]

=sum(if(($A$1:$A$5="Ford")*($B$1:$B$5="June"),1,0))

confirmed by a Ctrl+Shift+Enter

No need for the IF function:

=SUM(($A$1:$A$5="Ford")*($B$1:$B$5="June"))

Biff

 

[Guest]

Thank you everybody. They all work.

No need for the IF function:

=SUM(($A$1:$A$5="Ford")*($B$1:$B$5="June"))

Biff