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" wrote in message
...
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.