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,

=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!

I'm not clear...Are you asking for the non-subtotal versions of these formulas?

 

[Pixel_Donkey]

Yeah I think so. I filter the spreadsheet by month and the formula displays the filtered results. I also need a row that's not effected by the filter and shows results for all months.

Thanks.

 

[Aladin Akyurek]

Yeah I think so. I filter the spreadsheet by month and the formula displays the filtered results. I also need a row that's not effected by the filter and shows results for all months.

Thanks.

Control+shift+enter, not just enter:

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

Just enter one of:

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

 

[Pixel_Donkey]

Hi Aladin,

Thanks again.

My Year to Date averages are including the months not yet reached so it's adding a value of 0 for the empty fields which distorts my overall average.

Can I exclude fields where value equals nothing?

I've uploaded the spreadsheet here.

 

[Aladin Akyurek]

Hi Aladin,

Thanks again.

My Year to Date averages are including the months not yet reached so it's adding a value of 0 for the empty fields which distorts my overall average.

Can I exclude fields where value equals nothing?

I've uploaded the spreadsheet here.

Control+shift+enter:

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

 

[Pixel_Donkey]

Wonderful thank you again :D

 

[Aladin Akyurek]

Wonderful thank you again :D

You are welcome. Thanks for providing feedback.