Forum Discussion
Excel Countif with date and time
=SUMPRODUCT((MOD(A2:A19,1)<22/24)*(MOD(A2:A19,1)>14/24))
In this formula 22/24 is for 10 PM and 14/24 is for 2 PM. The range A2:A19 can be changed according to the actual size of the database. I can only guess that there is a text value in at least one cell in the range in column H. If your range is 1300 cell i'd reference e.g. H1:H1300 instead of H:H because the latter refers to 1048576 cells. It's easier to check if there is a text value in range H1:H1300.
When I plug in the formula and just change the range to H:H, it gives me a #value error
=SUMPRODUCT((MOD(A2:A19,1)<22/24)*(MOD(A2:A19,1)>14/24))
In this formula 22/24 is for 10 PM and 14/24 is for 2 PM. The range A2:A19 can be changed according to the actual size of the database. I can only guess that there is a text value in at least one cell in the range in column H. If your range is 1300 cell i'd reference e.g. H1:H1300 instead of H:H because the latter refers to 1048576 cells. It's easier to check if there is a text value in range H1:H1300.
OliverScheurich I wonder if you could help me with my own COUNTIFS issue.
I'm attempting to make COUNTIFS work in a way that will count how many cells from D2:P2 have dates in the past, but only if the date in C2 is in the future.
The formula I have so far is:
=COUNTIFS(C2,">="&TODAY(), D2:P2,"<="&TODAY())
This is returning #VALUE! and I can't seem to work out where I'm going wrong with the formula.
Thanks in advance.
=SUMPRODUCT((C2>=TODAY())*(D2:P2<=TODAY()))
SUMPRODUCT returns the intended result in my sheet if there are no empty cells in range D2:P2.
=SUMPRODUCT((C2>=TODAY())*(D2:P2<=TODAY())*(D2:P2<>""))
This formula works if i want to disregard empty cells in range D2:P2.
OliverScheurich Thank you so much. One last requirement that I forgot to mention:
C2 will either contain "N/A", a date in the future or a date in the past.
I'd like it to only count up for the cells that have dates in the future, not dates in the past, or N/A.
Thanks again.
