Filter Sum question

[TMILJVIPM]

What I need to do is only show the total in the cells I have when I filter a specific item. Here's what I have:

Cell R9 is for total manhours for all projects and the formula I have in it is =sum(R15:R104) so when I filter a certain project number I only want the totals for that specific project to show in cell R9. Now I have multiple cells that total different data but they all have the same formula.

Now 1 total cell which is W9 has formula =average(Y15:Y104).

I hope I explained properly

 

[Aladin Akyurek]

Have a look at the SUBTOTAL function.

 

[TMILJVIPM]

I havent had any luck with it

 

[Aladin Akyurek]

I havent had any luck with it

What does that mean exactly? Aren't you applying AutoFilter?

=SUBTOTAL(9,SumRange)

would sum the range to sum while AutoFilter is applied.

 

[TMILJVIPM]

What does that mean exactly? Aren't you applying AutoFilter?

=SUBTOTAL(9,SumRange)

would sum the range to sum while AutoFilter is applied.

Im not sure if im explaining properly. Let me try to explain some more.

Cell B15 thru B104 has project #'s ex 100111
Cell R15:R104 Has manhours
Cell R9 has Total manhours for all project #'s with the formula =sum(R15:R104)

But lets say I filter project # 100154 which would be in cell B74 I want cell R9 to pull the manhours for only project #100154. Right now my sheet still pulls all manhours for all jobs when I only filter specific project number.

 

[TMILJVIPM]

What does that mean exactly? Aren't you applying AutoFilter?

=SUBTOTAL(9,SumRange)

would sum the range to sum while AutoFilter is applied.

Never mind I got it. Duh I should have seen it. Some times things are so simple and yet they become so complicated looking for more complex answers.

Thanks for the patience

 

[Aladin Akyurek]

Im not sure if im explaining properly. Let me try to explain some more.

Cell B15 thru B104 has project #'s ex 100111
Cell R15:R104 Has manhours
Cell R9 has Total manhours for all project #'s with the formula =sum(R15:R104)

But lets say I filter project # 100154 which would be in cell B74 I want cell R9 to pull the manhours for only project #100154. Right now my sheet still pulls all manhours for all jobs when I only filter specific project number.

In R9 enter:

=SUBTOTAL(9,R15:R104)

would be equivalent to:

=SUMIF(B15:B104,100154,R15:R104)

When the filter is removed...

=SUBTOTAL(9,R15:R104)

would be equivalent to:

=SUM(R15:R104)

 

[TMILJVIPM]

In R9 enter:

=SUBTOTAL(9,R15:R104)

would be equivalent to:

=SUMIF(B15:B104,100154,R15:R104)

When the filter is removed...

=SUBTOTAL(9,R15:R104)

would be equivalent to:

=SUM(R15:R104)

OK I did run into 1 problem cell W9 has the formula =average(Y15:Y104)
so how would I handle this formula?

 

[MrVillareal]

Hi there try aggregate function if your using 2010 version. This function has the ability work on data that will ignore hidden rows. You can try sum,count, max, average etc..lots of function aggregated to one.

 

[TMILJVIPM]

Im working with 2007