Forum Discussion
Countif date in Column A is greater than date in Column B
matthew_frank , it could be
=SUMPRODUCT((Data!$B$2:$B$9>Data!$D$2:$D$9)*(A2=Data!$A$2:$A$9))
(adjust to actual range, or use dynamic ranges / tables) if only you don't keep Completed as text (zero or null cell). Text is always more than any number/date.
matthew_frank , it could be
=SUMPRODUCT((Data!$B$2:$B$9>Data!$D$2:$D$9)*(A2=Data!$A$2:$A$9))
(adjust to actual range, or use dynamic ranges / tables) if only you don't keep Completed as text (zero or null cell). Text is always more than any number/date.
Thank you - that works and gives me something to play with.
- Unfortunately I can't remove the "0" string as it's an artifact of how the data is produced, but I was able to handle it with *("0"<>Data!$B$2:$B$9))
- Re: Using a Dynamic Range: would that be something like using COUNTA determine how many rows are, so that any change in length is reflected?
I tried using B:B to simply say "Look at everything", but it failed. A simple-stupid alternative which worked was to simply say B2:B1000, since I'll never have anywhere near that, and it's still fast. But it would be nice to have it fully dynamic.
matthew_frank , you may use
=SUMPRODUCT((Data!$B$2:INDEX(Data!$B:$B,COUNTA(Data!$B:$B))>Data!$D$2:INDEX(Data!$D:$D,COUNTA(Data!$D:$D)))*(A2=Data!$A$2:INDEX(Data!$A:$A,COUNTA(Data!$A:$A)))*((Data!$B$2:INDEX(Data!$B:$B,COUNTA(Data!$B:$B))<>"0")))
Please see in attached
SergeiBaklanThank you - that helped immensely.