COUNTIF Question

# COUNTIF Question

• Friday, August 31, 2012 5:40 PM

Hello everyone,

I posted earlier concerning a SUMIF question which was kindly answered but now I have an almost identical COUNTIF problem which I can't seem to resolve with the same solution.

I have some text scattered randomly in some cells throughout column AW.

I also have the word "Seasonal" scattered randomy in cells thoughout column BA.

I want to COUNT all the instances where there is text in a cell in column AW, but only when they are in the same row as the word "Seasonal" in column BA.

Can someone help with this please?

Regards,

Pete

### All Replies

• Friday, August 31, 2012 6:20 PM

don't use countif in this case.

use: SUMPRODUCT()

Please do not forget to click “Vote as Helpful” if the reply helps/directs you toward your solution and or "Mark as Answer" if it solves your question. This will help to contribute to the forum.

• Friday, August 31, 2012 6:27 PM

or

=COUNTIF(A:W, AB1)

AB1 should be "Seasonal"

Please do not forget to click “Vote as Helpful” if the reply helps/directs you toward your solution and or "Mark as Answer" if it solves your question. This will help to contribute to the forum.

• Friday, August 31, 2012 7:32 PM

Hi Csarp,

I don't think COUNTIF is going to work for this example and I'm not sure how to use SUMPRODUCT, can you please help?

Regards,

Pete

• Friday, August 31, 2012 7:49 PM

Try This

=COUNTIFS(AW:AW,"Seasonal",BA:BA,"")

This will give you how many don't match Or you can change the "" to a real "value"

I haven't figured out how to copare against any existing value or has value yet but this is the basic constrcu you need

HTH

Chris Ward

• Edited by Friday, August 31, 2012 8:09 PM
•
• Friday, August 31, 2012 8:52 PM

Ahh.

You can try this

=((COUNTIF(AW:AW,"Seasonal"))-(COUNTIFS(AW:AW,"Seasonal",BA:BA,"")))

This tested and works in Excel 2007

HTH

Chris Ward

• Saturday, September 01, 2012 7:54 AM

I have some text scattered randomly in some cells throughout column AW.

I also have the word "Seasonal" scattered randomy in cells thoughout column BA.

I want to COUNT all the instances where there is text in a cell in column AW, but only when they are in the same row as the word "Seasonal" in column BA.

Suppose your range is from row 1 to 10000 :

=SumProduct((BA1:BA10000="Seasonal")*(AW1:AW10000<>0))

• Monday, September 03, 2012 5:06 AM

=SumProduct((BA1:BA10000="Seasonal)*(AW1:AW10000<>0))

=SumProduct((BA1:BA10000="Seasonal")*(AW1:AW10000<>0))

Corrected for missing "

This also works

Chris Ward

• Monday, September 03, 2012 5:48 AM

=COUNTIFS(AW:AW,"Seasonal",BA:BA,"<>")
will also work.

The Data Specialist (Blog)

• Monday, September 03, 2012 7:03 AM

=COUNTIFS(AW:AW,"Seasonal",BA:BA,"<>")
will also work.

The Data Specialist (Blog)

There it is. I knew there was a shorter syntax that would work.

Chris Ward