COUNTIF Question

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

Hi Csarp,

Thanks for your response.

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

Answer 4

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

Answer 5

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

Answer 6

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))

Answer 7

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

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

Corrected for missing "

This also works

---

Chris Ward

Answer 8

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

---

The Data Specialist (Blog)

Answer 9

=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