Q: 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?


A: 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.
