Countifs

[docjackson33]

I need to count a list of rank, and using the countifs seemed the proficient way, however I cant get it to work. Here's the formula I currently have, but it keeps returning a zero. I've tried the insert function but to no avail.

=COUNTIFS($E:$E,"PVT",$E:$E, "PV2",$E:$E, "PFC",$E:$E, "SPC",$E:$E, "CPL")

Thanks in advance,

Jonathan

 

[mole999]

This works

=COUNTIF(E:E,"PVT"), one for each variant

 

[barry houdini]

You could use this version

=SUM(COUNTIF($E:$E,{"PVT","PV2","PFC","SPC","CPL"}))

 

[docjackson33]

Thanks Barry, that's one less advil I'll need to take. Can you explain why the countifs is not working without the sum though?

 

[barry houdini]

With COUNTIFS the only rows counted are those that satisfy all the conditions so a formula like this would make sense

=COUNTIFS(A:A,"x",B:B,"y")

that will count rows where column A = "x" and column B = "y" but if you do this

=COUNTIFS(A:A,"x",A:A,"y")

....you'll always get zero because no cell can be "x" and "y"

With the version I suggested you are, in essence, using 5 COUNTIF formulas in one, the result of

=COUNTIF($E:$E,{"PVT","PV2","PFC","SPC","CPL"})

is an array of 5 values, e.g. if there were two PVTs in the range and three each of the rest that would give you this array

{2,3,3,3,3} and the SUM just sums those values to give the total

 

[docjackson33]

Hooah...Thanks again.