Originally Posted by
jnswbc
There is a date immediately above the INCOMP column header... Just as there is one above RECVD and COMP. However, you won't "see" the date because its the same color as the background color.
Sorry but that's not what is there. The date is only above the COMP columns. Formula box is blank:
blankcell.png
Another tip: For the day of the week, reference the date immediately below but format as Ddd. That will guarantee that the right dates are matched with the right days.
Looking at WOH, it looks like each row corresponds to a row in Completed Work. So I'm going to assume that for each row in WOH, you want the sum of the number in the corresponding row of Completed Work that fall into the INCOMP column with days older than 30 days prior to today. My original formula was the right idea. Here it is implemented in your file, but I did have to add the dates. I formatted them as you had fantasized
However, I couldn't really test it. You don't have any dates on Completed Work that are older than 30 days.
The formulas you tried have some problems in terms of how you used quotes.
This formula will look for a cell that has a value of "=TODAY()-1" and "=INCOMP". What you want is
This will match only yesterday's date.
In SUMIF and SUMIFS, the criteria value is a value or condition that must match values in the row or column to qualify to be included in the sum. Unfortunately, Microsoft has not done a good job of documenting exactly what options are available to use here.
If you just want to match a specific value, just use the value. It can be a number, string, or cell reference:
But you can also include comparisons. The way to do that is to build a string beginning with > < >= <=. (I have never tried to use just = but it's not necessary.)
will match all positive values. To build a string with a function or cell reference, use concatenation. This will match dates that are 30 days or more older than today:
Bookmarks