# How to use the SUM function on a filtered table

#### Rmayfield

##### New Member
I have 10 items in a table in Column A, rows A1 through A11 (with a header). Each item has a number associated with it in Column B. How do I add up those values when I filter the table. Example:

 Fruit # Apples 12​ Bananas 4​ Pears 3​ Red Grapes 7​ Oranges 14​ Strawberries 9​ Kiwi 1​ White Grapes 3​ Blueberries 8​ Pineapples 11​ Total 72​

How do I get the total to work when I filter?

 Fruit # Bananas 4​ Oranges 14​ Blueberries 8​ Total 72​

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### Eric W

##### MrExcel MVP
Use SUBTOTAL for that:

Book1
AB
1Fruit
3Bananas4
6Oranges14
8Kiwi1
10Blueberries8
12
13Total27
Sheet1
Cell Formulas
RangeFormula
B13B13=SUBTOTAL(109,B2:B11)

#### Fluff

##### MrExcel MVP, Moderator
You can use the Subtotal function
Excel Formula:
``=SUBTOTAL(109,B2:B11)``

#### Rmayfield

##### New Member
Use SUBTOTAL for that:

Book1
AB
1Fruit
3Bananas4
6Oranges14
8Kiwi1
10Blueberries8
12
13Total27
Sheet1
Cell Formulas
RangeFormula
B13B13=SUBTOTAL(109,B2:B11)

#### Rmayfield

##### New Member

Thank you both so much for the reply. It worked perfectly!

(What is 109 in the formula?)

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

Replies
3
Views
72
Replies
1
Views
196
Replies
1
Views
64
Replies
5
Views
80
Replies
9
Views
115

1,136,792
Messages
5,677,759
Members
419,718
Latest member
ALWP

### 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