How to modify SubTotal formula to also count cells with "DONE"?

Hello experts,
See my attached example.  I have formulas that count cells with dates and ignores N/A.  The formulas now need to include cells with "DONE".  (Formulas need to work when rows are filtered.)


Who is Participating?
barry houdiniConnect With a Mentor Commented:
I don't think COUNTIF will work because the count needs to look at the visible rows only. Try these Gary

..... in M28 copied across


and for M30


regards, barry
Gerwin Jansen, EE MVETopic Advisor Commented:
You mean the formulas in columns S and T? I opened your file and they are counting both the fields with date and "DONE"?
garyrobbinsAuthor Commented:
Sorry for not being more clear...  The formulas in rows 28 & 30.  Gary
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

COUNTIF is the command you looking for i think.

Look for CountIF
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

I don't quite understand the rules that lead to "Training required" = 13 in row 28 and at the same time have "Training current" with 10 in row 30.

From the formulas you're using I gather you could use in row 28


and in row 30

cheers, teylyn
garyrobbinsAuthor Commented:
Thanks, Barry!  That's what I wanted...

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.