count formula_2

hi experts, if i wanted to count everything other than "y" and say "z" within a cell range would the formula be =COUNTIF(B2:B5, "<>y,z")
Frank .SBuilding EstimatorAsked:
Who is Participating?
 
Ejgil HedegaardCommented:
You can count all, and then subtract the ones equal to elements in the list
=COUNTA(B2:B5)-SUMPRODUCT((B2:B5={"x","y","b","e","l","s"})*1)
0
 
Rgonzo1971Commented:
Hi,

pls try
=COUNTIFS(B2:B5, "<>y",B2:B5, "<>z") 

Open in new window

Regards
0
 
Frank .SBuilding EstimatorAuthor Commented:
hi rgonzo1971, is there a formula that can only count anything other than "y" or "z" values, the reason i ask this is what if i want a count for all values except for x, y, b, e, l, s? is the only solution having to add every value separately like you have in your formula?
0
 
Frank .SBuilding EstimatorAuthor Commented:
hi experts when using this formula on the row instead of the column, it produces a #N/A error?
=COUNTA(H22:O22)-SUMPRODUCT((H22:O22={"y","-"})*1)

could you tell me what I need to fix?
0
 
Ejgil HedegaardCommented:
Use
=COUNTA(H22:O22)-SUMPRODUCT((H22:O22={"y";"-"})*1)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.