• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

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
0
garyrobbins
Asked:
garyrobbins
1 Solution
 
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
 
peter197911Commented:
COUNTIF is the command you looking for i think.
http://www.contextures.com/xlfunctions04.html

Look for CountIF
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
barry houdiniCommented:
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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now