How to use the SUM function on a filtered table

Rmayfield

New Member
Joined
Feb 19, 2021
Messages
13
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Aug 18, 2015
Messages
11,123
Use SUBTOTAL for that:

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,131
Office Version
  1. 365
Platform
  1. Windows
You can use the Subtotal function
Excel Formula:
=SUBTOTAL(109,B2:B11)
 

Rmayfield

New Member
Joined
Feb 19, 2021
Messages
13
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

(What is 109 in the formula?)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,131
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top