How do I write a nested COUNTIF formula?-CodePudding

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?

CodePudding user response：

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,

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

With helper showing that it gives the same output,

• Formula used in cell C2

=DATEDIF(A2,B2,"d")

And Fill Down!

• Formula used in cell D2

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

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.