countifs not returning correct value

[MP1989]

Hi,

just wanted to understand why this formula doesn't return the value i want. I would be grateful if you could explain/fix the countifs statement, however I have a sumproduct that does the same job but apparently they are slower to run.

this is the countifs statement - the bold section is the bit that doesn't "work" I have two columns of data that this is looking at - Action limit contains a limit which is different on some rows (i.e I1 = 1, I2 = 2, I3 = 5) and count is a number (K1 = 1, K2 = 0, K3 = 2). I want the formula to compare the action limit on that particular row with the count and if the count is greater than or equal return a 1. So this example should return 1, but instead returns 2. This seems to just look at the first row action limit and compare that against all counts rather than each row separately.

Please Login or Register  to view this content.

This works

Please Login or Register  to view this content.

Hope this makes sense and grateful for any help as always.

[ben_hensel]

I think you've got the range vs. values swapped in the syntax of the COUNTIFS?

This seems to just look at the first row action limit and compare that against all counts rather than each row separately.

That is very much how COUNT is designed to work. It compares a range to a given value, and returns a match (or greater/equal/less than) for each step down the range against that one value. It's not supposed to be doing any math comparing the values in two ranges at each index.

Anyway, SUMPRODUCT will be... 3-to-5 times slower is the number I've seen thrown around, but I haven't done any testing myself. So unless you're up in more than 100,000 cells long in the ranges, it shouldn't matter much. If you're down in the thousandish territory for your table definition, you likely won't have a human-detectable difference in calc time.