# Filters and Subtotal(9,A1:A1000)

#### gheyman

##### Well-known Member
Is there a way to create the type of formual that "Subtotal" is (Where it does not include filtered rows in its total) with a SumIf Formula?

I want the SumIf formula to only add the values where its corresponding cell in column E = Yes. But I dont want the total to include the row where E is Yes, but the column is hidden.

Hope thats clear enough.

Thanks
Greg

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

##### MrExcel MVP
Is there a way to create the type of formual that "Subtotal" is (Where it does not include filtered rows in its total) with a SumIf Formula?

I want the SumIf formula to only add the values where its corresponding cell in column E = Yes. But I dont want the total to include the row where E is Yes, but the column is hidden.

Hope thats clear enough.

Thanks
Greg

Which range do you want to sum while AutoFilter is on?

#### gheyman

##### Well-known Member
I am looking down column E9:E1000 for "Yes" and totaling value from column G9:G1000

I just used A as an example in my explaination. E and G are the actual columns being used in the formula

##### MrExcel MVP
I am looking down column E9:E1000 for "Yes" and totaling value from column G9:G1000

I just used A as an example in my explaination. E and G are the actual columns being used in the formula

Try...

=SUMPRODUCT(SUBTOTAL(9,OFFSET(G9,ROW(G9:G1000)-ROW(G9),,1)),--(E9:E1000="Yes"))

#### gheyman

##### Well-known Member
Thanks Very much.

Both samples worked. Two approaches and both come up with the same answer.

I need to do my homework on SUMProduct.

Replies
10
Views
88
Replies
3
Views
253
Replies
9
Views
147
Replies
1
Views
288
Replies
3
Views
329

1,191,719
Messages
5,988,292
Members
440,148
Latest member
sandy123

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back