I'm trying to count cells that start with a certain date equal to a cell using sumproduct which works fine unless the array includes blanks.
Through a lot of google searches and stackoverflow searches, i've managed to come up with the below code, i've tried at least 20-25 variants with none giving the intended result, including CSE/array formulas
Evaluating the formula shows that the second array is keeping the blanks and giving a #Value error which i can't remove from the array.
=SUMPRODUCT((BB3:BB33)<>"",--(DAY(BB3:BB33)=DAY(A38)))
A38 is in the format 11/09. Range BB3:BB33 is dates (with times unsplit due to how data is pulled)
11/09/2019 08:14
11/09/2019 08:14
10/09/2019 23:20
10/09/2019 23:20
10/09/2019 23:20
10/09/2019 23:20
10/09/2019 23:20
10/09/2019 23:20
BLANK
BLANK
etc, etc.
the output should be 2 for A38 which when the range only includes non-blanks is correct but the blanks override the result to #Value
Any help would be greatly appreciated!
Thanks!
I've FINALLY managed to solve this with a much simpler solution (After many many attempts). This ignores the blanks completely which were the cells causing issues for sumproduct.
=COUNTIFS(BB3:BB33,">"&DATEVALUE(A38))
Doesn't require CSE.