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

Help?

Gary
Training-Records-Planner-Master-.xls
garyrobbinsAsked:
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

=SUBTOTAL(2,M10:M25)+SUMPRODUCT((M$10:M$25="Done")*SUBTOTAL(3,OFFSET(M$9,ROW(M$10:M$25)-ROW(M$9),0,1,1)))

and for M30

=SUMPRODUCT((M$10:M$25>=$A$2)*ISNUMBER(M$10:M$25)+(M$10:M$25="Done"),SUBTOTAL(3,OFFSET(M$9,ROW(M$10:M$25)-ROW(M$9),0,1,1)))

regards, barry
0
 
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"?
0
 
garyrobbinsAuthor Commented:
Sorry for not being more clear...  The formulas in rows 28 & 30.  Gary
0
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.

 
peter197911Commented:
COUNTIF is the command you looking for i think.
http://www.contextures.com/xlfunctions04.html

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

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

=COUNTIF(M10:M25,"<>NA")

and in row 30
=SUMPRODUCT(((ISNUMBER(M$10:M$25))*(M$10:M$25>=$A$2))+(M$10:M$25="Done"),--(M$10:M$25<>""))

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

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