Need Help on filtering

[foxwerks]

Hello all long time lurker first time poster.

I've hit a dead end with a project I'm currently working on. Its really starting to get complex and I need some help.

For Total Annual I am using =COUNTIF(D9:D76, "A") Sick is just the same replaced with "S" and so forth
For Allotment I am using =SUM(D9:D76)+D78+D79+D80+D81+D82
For Working I am using =COUNTBLANK(D9:D76)

The results show all of the shifts not just the ones i'm filtering out. I want the results to sync with the filtering. I was wondering is any solution to this? I've been going mad for a couple of days to try to figure this out.

Any help is appreciated thanks!

 

[CharlesH]

HI,

Can you post an attachment for this file?

 

[Marcelo Branco]

Maybe

Total Annual (D78)
=SUMPRODUCT(--(SUBTOTAL(3,OFFSET($A$9:$A$76,ROW($A$9:$A$76)-ROW($A$9),0,1))),--(D$9:D$76="A"))

Replace "A" by "S" for Sick and so on

Allotment (D84)
=SUM(D$78:D$82)

Working (D85)
=SUMPRODUCT(--(SUBTOTAL(3,OFFSET($A$9:$A$76,ROW($A$9:$A$76)-ROW($A$9),0,1))),--(D$9:D$76=""))

Hope this helps

M.

 

[foxwerks]

Maybe

Total Annual (D78)
=SUMPRODUCT(--(SUBTOTAL(3,OFFSET($A$9:$A$76,ROW($A$9:$A$76)-ROW($A$9),0,1))),--(D$9:D$76="A"))

Replace "A" by "S" for Sick and so on

Allotment (D84)
=SUM(D$78:D$82)

Working (D85)
=SUMPRODUCT(--(SUBTOTAL(3,OFFSET($A$9:$A$76,ROW($A$9:$A$76)-ROW($A$9),0,1))),--(D$9:D$76=""))

Hope this helps

M.

OMG thank you so very much! I've been seeing that type of string on google but I don't understand any of it, so i kept moving on. I just plugged the values in and it worked! Is there an explanation for this?

 

[PaddyD]

see:

The Spreadsheet Page - Excel Expert E-Letter: Issue No. 01 (March 16, 1999)

 

[Marcelo Branco]

OMG thank you so very much! I've been seeing that type of string on google but I don't understand any of it, so i kept moving on. I just plugged the values in and it worked! Is there an explanation for this?

You are welcome and thanks for the feedback.

About the formula
Apart the link PaddyD has provided you can also see
Daily Dose of Excel » Blog Archive » Arrays with Offset

XL-CENTRAL.COM : Excel : Count Based on a Single Criteria in a Filtered List

M.