Count or sum product of two columns to determine overdue and show the overdue as on hold

Good afternoon All,

Moderators I apologize if the title is not descriptive enough, or if the post asks too many questions. I'm not trying to be difficult, I really like this site and the help.

Need some more help with this tracking sheet. I had previously asked the overdue question a few days ago but for some reason neither formula works now. Not sure how I messed it up.

Cell C69/70 are the two formulas that are currently not working for me, I only need one total overdue count. The cell needs to show a shipment that went out late (column H is greater than G) and if todays date is greater than column G.

Cell C71 should show overdue that is on hold (h) there are only four; B29, 47, 50, & 58.

Cell C78 needs to show the overdue for just Edger's (e) shipments.

Any links would be greatly appreciated so I can learn this myself and not bother anyone. Any suggestions on how to send this summary page to powerpoint? Again thank you everyone for your help in advance.

BB4523

Here is a response to the C69/C70 problem:

For reference, the thread mentioned above is:
https://www.excelforum.com/excel-gen...ml#post5574294

Please try the following in C69 and C70

Formula:

Please Login or Register  to view this content.

This yields 20 overdue items. Your expected result is 21 but please check row-9 - as far as I can see that row is NOT overdue.

The above formula is josephteh's formula from the above mentioned thread with the red highlighted changes (the first is a less than or equal changed to just less-than. The second change was the addition of the today() check.

I'll look now at the other parts to your question.

Cell C71 should show overdue that is on hold (h) there are only four; B29, 47, 50, & 58.

Please clarify. Where is the data to count the items on hold??

Cell C78 needs to show the overdue for just Edger's (e) shipments.

I'm confused with this also. Per post #3 in https://www.excelforum.com/excel-gen...ml#post5574294 the formula you requested includes just "e". That's what the suggested C69 formula already does.

If in C69 you want ALL overdue items, then just remove the "e" conditions so that the C69 formula would now be:

Formula:

Please Login or Register  to view this content.

The [hold] data is in column B identified with an h

If you can have items on hold that are not overdue then in C71:

Formula:

Please Login or Register  to view this content.

This is just the "e" overdue formula except it checks for "h" in col-B instead of "e" in col-F

Question Cell C78 needs to show the overdue for just Edger's (e) shipments.
------- You are correct they should be the same, but C78 = 11 and C70 = 20.

C78 looks like it has my incorrect formula from the previous thread. That's the problem. Just simply copy the formula from C70 to C78. Be careful how you do that though. All of the range references in the C78 really should have been absolute. Just make sure when you're done that the range stays from row 3 to row 64.

C69 help: After entering the formula the result is 26, but after going cross eye'd now all I can count is 25. Please show me my error!

As I don't know which 25 you have counted I can't say which is the one you are missing

What will help you find the missing item is what I did in order to identify the overdue items:
Take the C69 formula and copy it to M3. As noted above, make sure the formula has kept the row3 to row 64 range (or just change the ranges to absolute in C69 before you copy).
Now remove the "sumproduct(" from the start of the M3 formula along with the final ")".
Copy the M3 formula down to M64. You should now see either a "0" or a "1" in column-M. The 1's show which rows are contributing to the count of 26 and it should be easy for you to spot the one you are missing.

Hopefully the above is clear. If not, I have attached an update to your workbook with the changes made.

@julianin

This is not a very helpful post, in fact it does not help anyone nor does it move the thread forward. Please refrain from posting unless you have something constructive to offer.

To ask a question, please start your own thread.

Thank you.

Thanks for the feedback and added reputation

To transfer your summary to PowerPoint, one way: select the summary cells you want then "copy" then in PowerPoint "paste-special" (I always found that "enhanced metafile" worked best for me). If that is not answering your question then - yes - you should open a new thread!