0
votes

I am looking to use COUNTIF or COUNTIFS to add up a set of 7 cells where each cell could trigger 2 different criteria.

The Cells contain numbers and I need the counter if to see if the cell is greater than 0 OR lower than an other static cell.

Most tutorials and examples I can find either describe AND statements or end up getting the cell counted twice as it triggers both criteria if the number is between the 2 comparison factors.

Here is some pseudo code of what I am looking for.

COUNTIF(Balance, is greater than 0 OR greater than last months payment) then count cell ONCE

Please ask any questions and pre-thanks for any responses and time taken to read my question.

-------------------EDIT-------------------------

Don't worry answered by own question.

All hail the rubber duck method.

--------------Second Edit 07/04/15----------------

Thank-you for the responses. I had solved the issue about 30 secs after posting the question and did post an edit but this was "Revised" by a user called Mike? and removed.

2
+1, I too would like to see your solution so far. And take a look at my answer. It should enhance your old approach to get the expected behavior.mike

2 Answers

0
votes

I realized that I could just ask if the balance was less that since it is impossible for the balance to go less than 0 it was just a matter of checking that it was lower than last months payment.

I was too busy trying to build in checks that the balance wasn't below 0 to remember that other factors in the system prevented it.

All I needed was to write the problem down for the answer to trigger in my head.

0
votes

APPROACH 1: ROUND

Take your mentioned solution that counts the cell twice, divide the result by 2 and round it up.

COUNTIF_VALUE     DIVIDE_BY_2     AFTER_CEIL
0                 0               0          # no criteria triggered
1                 0.5             1          # one criteria
2                 1               1          # both criteria were matched

APPROACH 2: MODULO

...or if no ceiling function is available (and I am not sure right now), use follwing approach.

COUNTIF_VALUE     DIVIDE_BY_2     TIMES_10   ADD_MODULO_10  DIVIDE_BY_10
0                 0               0          0              0
1                 0.5             5          10             1
2                 1               10         10             1