Search code examples
excelexcel-formularangecriteriacountif

Countif(s) criteria is a range plus a number

I would like to countif(s) a range is greater than another range plus 3.

Here is what I currently have that isn't working (only the 3rd criteria isn't working):

=COUNTIFS($F:$F, ">" &G:G,F:F, ">1",F:F, "<" &G:G+3)

Isolating the issue to a single row works, a la:

=COUNTIF(F12, "<" &G12+3)

I am trying to get the countifs to check if an F cell in a given row is less than a G cell +3 for the same row.

Solution

  • You need a formula that supports arrays for that, like the following, entered with ctrl+shift+enter, instead of the regular Enter

    =SUM(IF(ISERROR(--$F1:$F100),0,IF(ISERROR(--$F1:$F100),0,IF($F1:$F100>G1:G100,IF(F1:F100>1,IF(F1:F100<G1:G100+3,1,0),0),0))))