# 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
###### Who is Participating?

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

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

Author Commented:
Sorry for not being more clear...  The formulas in rows 28 & 30.  Gary
0

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

Look for CountIF
0

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

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