counting 2 words in 1 row

[mwvirk]

i want to find 2 words in 1 row and count the total using if condition. but don't know what is the problem here:
=COUNTIFS(X4:X230,"Resolved",X4:X230,"Pending")

pls note that above mentioned row range already contains some other formulas.
thnx

 

[Jonmo1]

Countifs counts only if BOTH conditions are TRUE
NOT if one OR the other is True

Try

=SUMPRODUCT(--(ISNUMBER(SEARCH({"Resolved","Pending"},X4:X230))))


Change Search to Find if you want it to be case sensitive.


Hope that helps.

 

[Jonmo1]

Or even better

=SUMPRODUCT(--(X4:X230={"Resolved","Pending"}))

 

[T. Valko]

i want to find 2 words in 1 row and count the total using if condition. but don't know what is the problem here:
=COUNTIFS(X4:X230,"Resolved",X4:X230,"Pending")

pls note that above mentioned row range already contains some other formulas.
thnx

Try one of these...

=SUM(COUNTIF(X4:X230,{"Resolved","Pending"}))

This version gives you more flexibilty by using cells to hold the criteria:

• A1 = Resolved
• A2 = Pending

=SUMPRODUCT(--(ISNUMBER(MATCH(X4:X230,A1:A2,0))))

 

[T. Valko]

Try one of these...

=SUM(COUNTIF(X4:X230,{"Resolved","Pending"}))


This version gives you more flexibilty by using cells to hold the criteria:

• A1 = Resolved
• A2 = Pending

=SUMPRODUCT(--(ISNUMBER(MATCH(X4:X230,A1:A2,0))))

Of course, you could always do:

=COUNTIF(X4:X230,A1)+COUNTIF(X4:X230,A2)

 

[mwvirk]

thanks. its working fine. but since we are talking about case sensitive (which i like bcoz i want these words in proper order)

can u help me here: =COUNTIF(X4:X230,"Resolved")
formula/condition is ok. only i want to use case sensitive search
thnx