SOLVED

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

Copper Contributor

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 completed "Completed Date".  It also contains a column for the type of work, "Type".

I'd like to count - via a formula (and not VBA) - how many items were completed late, for each type of work.  I've tried using COUNTIF and SUMPRODUCT but have not been able to figure it out, and would appreciate any help.

The "Results" sheet has sample results showing that for the "Required" type of work there are 3 late items, and for the "Suggested" type of work there's 1 late item.

EDIT: When working with COUNTIFS my problem was that I couldn't, or didn't know how, to reference a different row so that I could say "If the date in this column is greater than the date in that column..."

I tried a convoluted method of getting the offset of the cell with INDIRECT and ADDRESS and so on, but while that worked in a test, it did not work within the context of a COUNTIF.

4 Replies
best response confirmed by matthew_frank (Copper Contributor)
Solution

@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

 

@Sergei BaklanThank you - that helped immensely.

1 best response

Accepted Solutions
best response confirmed by matthew_frank (Copper Contributor)
Solution

@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.

 

View solution in original post