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