Forum Discussion

matthew_frank's avatar
matthew_frank
Copper Contributor
Apr 16, 2019
Solved

Countif date in Column A is greater than date in Column B

I've attached a sample xlsx file containing 2 sheets, "Data" and "Results".  The "Data" sheet contains columns for the date something was scheduled, "Scheduled Date", and the date it was actually ...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Apr 16, 2019

    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's avatar
matthew_frank
Copper Contributor
Apr 16, 2019

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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 16, 2019

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

 

Resources