How to use the SUM function on a filtered table

[Rmayfield]

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​

 

[Eric W]

Use SUBTOTAL for that:

Book1
	A	B
1	Fruit
3	Bananas	4
6	Oranges	14
8	Kiwi	1
10	Blueberries	8
12
13	Total	27
Sheet1

Cell Formulas
Range	Formula
B13	B13	=SUBTOTAL(109,B2:B11)

 

[Fluff]

You can use the Subtotal function

=SUBTOTAL(109,B2:B11)

 

[Rmayfield]

Use SUBTOTAL for that:

Book1
	A	B
1	Fruit
3	Bananas	4
6	Oranges	14
8	Kiwi	1
10	Blueberries	8
12
13	Total	27
Sheet1

Cell Formulas
Range	Formula
B13	B13	=SUBTOTAL(109,B2:B11)

 

[Rmayfield]

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

(What is 109 in the formula?)

 

[Fluff]

It means to Sum ignoring filter & manually hidden rows, you can see what all the options are here How to use the Excel SUBTOTAL function | Exceljet

 

[Rmayfield]

It means to Sum ignoring filter & manually hidden rows, you can see what all the options are here How to use the Excel SUBTOTAL function | Exceljet

Thank you

 

[Fluff]

Glad we could help & thanks for the feedback.