Counting rows that meet conditions in multiple columns

[belleinhell]

I want to count how many people who attended an event hold a certain status.
I've tried a number of formulas, including:

=SUMPRODUCT((C2:C493="x")*(A2:A493="renewed"))
and =SUMPRODUCT(--(C2:C493="x"),--(A2:A493="renewed"))
(the "x" marks whether someone attended the event)

=SUMPRODUCT(IF(ISNUMBER(SEARCH("Asia Trail", B1:B493)),
IF(A2:A493="renewed",1)))
where "Asia Trail" is one of several text values in a cell

and I continue to get incorrect numbers. In other similar cells, I get zero
when just looking at the sheet I know the number has to be in the 100's.

 

[M Kan]

Odd, your second formula works just fine for me, which would make me think
that may be something is going on with the inputs. Are there extra spaces in
the "x" or in the "renewed"?

 

[Peo Sjoblom]

The SUMPRODUCT with the IF formula won't work, you would use

=SUMPRODUCT(--(ISNUMBER(SEARCH("Asia Trail",
B2:B493))), --(A2:A493="renewed"))

also the ranges need to be of equal size you are using B1:B493 and A2:A493

The other formulas should work so you must have leading trailing
spaces/hidden characters in your data



--


Regards,


Peo Sjoblom

 

[belleinhell]

I tried your formula and still no success. I also tried:
=SUMPRODUCT((ISNUMBER(SEARCH("Asia Trail", B2:B493)))*(A2:A493="renewed"))
=SUMPRODUCT(--(ISNUMBER(SEARCH("Asia
Trail",B2:B493))),--(ISNUMBER(SEARCH(A2:A493,"renewed"))))
=SUMPRODUCT((ISNUMBER(SEARCH("Asia",B2:B493)))*(ISNUMBER(SEARCH(A2:A493,"renewed"))))
and none of those worked either. I'm going to try redoing the worksheet and
see if that does anything.