Search code examples
excelcountifsumproduct

How do I write a nested COUNTIF formula?

There is column A for check-in date, column B for check-out date for 10 individuals(10 rows of data). Find how many people stayed over 5 days WITHOUT using a helper column.

What I tried (doesn't work)---

=COUNTIF(DATEDIF(A2:A11,B2:B11, "d"), ">5")

This gave me an error. Is there a right way to do it?

If I use a helper column, C = DATEDIF(A2,B2,"d"), and then do =COUNTIF(C2:C11, ">5") it works

But how do I get the answer without the helper column?

enter image description here

Solution

  • You may try something like this, COUNTIF() Function works with a range, while SUMPRODUCT() Function with arrays, so its best to use the latter one,

    FORMULA_SOLUTION

    =SUMPRODUCT(--(($B$2:$B$11-$A$2:$A$11)>5))

    With helper showing that it gives the same output,

    HELPER_SOLUTION

    • Formula used in cell C2

    =DATEDIF(A2,B2,"d")

    And Fill Down!

    • Formula used in cell D2

    =COUNTIF($C$2:$C$11,">5")

    FORMULA_SOLUTION

    COUNTIF() doesn't accept array constants (as far as I know).

    • Formula used in cell A17

    =SUM(IF(B2:B10-A2:A10>7,1,""))

    You can also create a COUNTIF() type formula like this (the combination CTRL + SHIFT + ENTER):

    • Formula used in cell A15

    =COUNT(IF(B2:B10-A2:A10>7,1,""))

    Note: The use of double negative --> "double unary" which coerces TRUE or FALSE values to their numeric equivalents, 1 and 0. It's used in formulas where numbers are needed for a particular math operation.