Subtotal But Exclude Certain Rows

[Pixel_Donkey]

Hello,

I'm having a problem excluding data from my Subtotal:

I need to make the subtotal only include:

• Stone
• Fabric
• Glass
• Paper

And Exclude:

• Granite
• Marble
• Pebble
• Cobble
• Brick

How can I do this with a SUBTOTAL or SUMIF statement?

If a SUMIF statement is used I would need to exclude the Hidden rows as I filter this spreadsheet by Date.

Thanks for any help.

 

[Aladin Akyurek]

Hello,

I'm having a problem excluding data from my Subtotal:

I need to make the subtotal only include:

• Stone
• Fabric
• Glass
• Paper

And Exclude:

• Granite
• Marble
• Pebble
• Cobble
• Brick

How can I do this with a SUBTOTAL or SUMIF statement?

If a SUMIF statement is used I would need to exclude the Hidden rows as I filter this spreadsheet by Date.

Thanks for any help.

If you are applying AutoFilter on the Date range and you want to sum the Value range for Stone, Fabric, Glass, and Paper together...

=SUMPRODUCT( 
    SUBTOTAL(9,OFFSET(C2, ROW(C2:C10)-ROW(C2),,1)), 
    --ISNUMBER(MATCH(A2:A10,{"Stone","Fabric","Glass","Paper"},0))))

If no AutoFilter applied...

Either:

=SUM(SUMIF(A2:A10,{"Stone","Fabric","Glass","Paper"},C2:C10))

Or:

=SUMPRODUCT(
    C2:C10, 
    --ISNUMBER(MATCH(A2:A10,{"Stone","Fabric","Glass","Paper"},0))))

 

[Pixel_Donkey]

That answered it perfectly, thank you very much!

One more question, I'm using:
=SUMPRODUCT(SUBTOTAL(9,OFFSET(C2,ROW(C2:C100)-ROW(C2),,1)),--ISNUMBER(MATCH(A2:A100,{"Stone","Fabric","Glass","Paper"},0)))

9 is a subtotal, how can I change this to an average? Simply changing 9 breaks the formula.

 

[Pixel_Donkey]

That answered it perfectly, thank you very much!

One more question, I'm using:
=SUMPRODUCT(SUBTOTAL(9,OFFSET(C2,ROW(C2:C100)-ROW(C2),,1)),--ISNUMBER(MATCH(A2:A100,{"Stone","Fabric","Glass","Paper"},0)))

9 is a subtotal, how can I change this to an average? Simply changing 9 breaks the formula.

This was quite simple to sort out I just used

=SUMPRODUCT(SUBTOTAL(9,OFFSET(C2,ROW(C2:C100)-ROW(C2),,1)),--ISNUMBER(MATCH(A2:A100,{"Stone","Fabric","Glass","Paper"},0)))/4

 

[Pixel_Donkey]

Sorry but it didn't work as easily as I hoped.

If I show more than one month /4 doesn't work for the average because 2 months is /8

Any ideas?

 

[Pixel_Donkey]

I still haven't been able to find an answer for this one

Can anyone please help?

 

[Aladin Akyurek]

I still haven't been able to find an answer for this one

Can anyone please help?

Control+shift+enter, not just enter:

=AVERAGE(
   IF(SUBTOTAL(2,OFFSET(C2,ROW(C2:C10)-ROW(C2),,1)),
   IF(ISNUMBER(MATCH(A2:A10,{"Stone","Fabric","Glass","Paper"},0)),
     $C$2:$C$10)))

 

[Pixel_Donkey]

Hi Aladin,

That is amazing, thank you - it's really helped me out

 

[Aladin Akyurek]

Hi Aladin,

That is amazing, thank you - it's really helped me out

You are welcome. Thanks for providing feedback.

 

[Pixel_Donkey]

Hello,

=AVERAGE(
   IF(SUBTOTAL(2,OFFSET(C2,ROW(C2:C10)-ROW(C2),,1)),
   IF(ISNUMBER(MATCH(A2:A10,{"Stone","Fabric","Glass","Paper"},0)),
     $C$2:$C$10)))

=SUMPRODUCT(SUBTOTAL(9,OFFSET(C2,ROW(C2:C100)-ROW(C2),,1)),--ISNUMBER(MATCH(A2:A100,{"Stone","Fabric","Glass","Paper"},0)))

Is there anyway I can turn these into sum's instead of a subtotal. I need to add a line that has the yearly total, not just the filtered subtotal.

Cheers!