200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Worksheet "Example Weekly Backlog" - Tab "AWD Weekly Dashboard"
Cells f5: I need a formula that will count the age betweeon 30-60 in "Aged Data Tab" column H:H to include all data in that column. I need that to also only count anything that matches Blank in column N:N
Cells: I would need the exact same rules set for cell F5 (blank in column N) for the following cells below but follow the rules above:
G5 (60-90 Days)
H5 (90-180 Days)
I5 (180 days and larger)
If you count groups comprising 30 to 60 and 60 to 90 items aged 60 will be included in both. Therefore you might prefer to exclude straight 60's from the first group and include them only in the second. This is what the formula below does.
=COUNTIFS('Aged Data'!$H:$H,">=30",'Aged Data'!$H:$H,"<60")The group comprising 180 days and more doesn't need COUNTIFS. Observe that items of straight 180 days would be included here.
=COUNTIF('Aged Data'!$H:$H,">=180")Note that both above formulas look at the entire column and would, therefore, pick up on any numbers contained in the column captions.
To count the number of instances of the word "Blank" in column N is a separate COUNTIF action which can be included in the total shown in one cell by simple addition.
=COUNTIFS('Aged Data'!$H:$H,">=30",'Aged Data'!$H:$H,"<60")+COUNTIF('Aged Data'!$N:$N,"blank")I have spelled out the word "Blank" in lower case in the formula to indicate that its use isn't case sensitive. COUNTIF will count "blank", "Blank" or "BLANK".