Apr 16 2019 09:04 AM - edited Apr 16 2019 09:15 AM
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.
Apr 16 2019 09:28 AM
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.
Apr 16 2019 10:29 AM - edited Apr 16 2019 10:44 AM
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.
Apr 16 2019 11:24 AM
@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
Apr 16 2019 11:42 AM
@Sergei BaklanThank you - that helped immensely.
Apr 16 2019 09:28 AM
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.