Afia Kona
If you are looking for Excel sum visible cells with criteria, you have come to the right place. Here, we will walk you through 5 easy and effective methods to do the task smoothly.
Download Practice Workbook
You can download the Excel file and practice while you are reading this article.
5 Methods to Sum Visible Cells with Criteria in Excel
The following dataset has the Company, Color Code, and Profit Columns. Using this dataset, we will go through 5 methods for Excel sum visible cells with criteria.
Here, we used Microsoft Excel 365. You can use any available Excel version.
1. Use of SUBTOTAL Function to Sum Visible Cells with Criteria
In this method, we will use the SUBTOTAL function to sum visible cells with criteria. Here, first, we will create a table using the following dataset, and after that, we will show the sum of the Profit based on the Green color code only.
Let’s go through the following steps to do the task.
Step-1: Inserting Table
In this step, we will insert a Table.
- First of all, we will select the entire dataset from cells B4:D10.
- After that, we will go to the Insert tab >> select Table.
After that, a Create Table dialog box will appear.
Make sure My table has headers marked.
- Then, click OK.
As a result, you can see the Table.
Step-2: Using SUBTOTAL Function
In this step, we will use the SUBTOTAL function to sum visible cells with criteria.
- In the beginning, we will type the following formula in cell D11.
=SUBTOTAL(9,[Profit])Here, the SUBTOTAL function yields the summation of the Profit column.
- After that, press ENTER.
As a result, you can see the result in cell D11.
Next, we will Filter the Table and we will keep only the Green color code.
- Afterward, we will click on the drop-down arrow of the heading Color Code in cell C4.
- Next, we will unmark Red color >> click OK.
Therefore, you can see the Table now shows a Green color code only.
Along with that, cell D11 shows the sum of the visible cells only.
2. Applying SUMIF and AGGREGATE Functions
In this method, we will use the SUMIF and AGGREGATE functions to sum visible cells with criteria.
Here, we will find the Profit based on the color Red.
Steps:
- In the beginning, we will insert Assistant Column into the dataset.
- After that, we will type the following formula in cell E5.
=AGGREGATE(9,5,D5)The AGGREGATE function yields an aggregate in a data table.
- Afterward, press ENTER.
Then, you can see the result in cell E5.
- Furthermore, we will drag down the formula with the Fill Handle tool.
As a result, you can see the complete Assistant Column.
- Moreover, we will type the following formula in cell C13.
=SUMIF(C5:C10,"Red",E5:E10)The SUMIF function yields the summation of the values of a range of cells that meet the criteria we specify.
- At this point, press ENTER.
Therefore, you can see the result in cell C13.
- After that, we followed Step-1 of Method-1 to insert a Table.
Therefore, you can see the Table.
- Next, we will click on the drop-down arrow of the heading Company in cell B4.
- After that, we unmark A >> click OK.
As a result, you can see Company A has been hidden.
Along with that, you can see the sum visible cells with criteria in cell C13.
Similar Readings
- How to Paste into Visible Cells Only in Excel (5 Easy Methods)
- Average Only Visible Cells in Excel (3 Quick Methods)
- How to Copy Visible Cells Only without Header Using VBA
- How to Select Visible Cells in Excel (5 Quick Tricks)
3. Using Combined Functions
In this method, we will use the combination of SUMPRODUCT, SUBTOTAL, OFFSET, ROW, and MIN functions to sum visible cells with criteria.
- First, we will type the following formula in cell C13.
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C5:C10,ROW(C5:C10)-MIN(ROW(C5:C10)),,1)),(C5:C10=C12)*(D5:D10))
Formula Breakdown
- ROW(C5:C10) → the ROW function yields the row number of a range.
- MIN(ROW(C5:C10)) → the MIN function  returns the minimum row number in a sequence.
- Output: 5
- OFFSET(C5:C10,ROW(C5:C10)-MIN(ROW(C5:C10)),,1) → the OFFSET function returns a section from a data set with a specific height and a specific width, situated at a specific number of rows down and a specific number of columns right from a given cell reference.
- SUBTOTAL(3,OFFSET(C5:C10,ROW(C5:C10)-MIN(ROW(C5:C10)),,1)) → the SUBTOTAL function yields the summation of the Profit
-  SUMPRODUCT(SUBTOTAL(3,OFFSET(C5:C10,ROW(C5:C10)-MIN(ROW(C5:C10)),,1)),(C5:C10=C12)*(D5:D10)) → the SUMPRODUCT function yields the summation of products of a range.
- Output: $3900
- Explanation: Here, $3900 is the sum of the Profit based on the color RED.
- Â Afterward, press ENTER.
- As a result, you can see the result in cell C13.
- After that, we followed Step-1 of Method-1 to insert a Table.
Therefore, you can see the Table.
- Next, we will click on the drop-down arrow of the heading Company in cell B4.
- After that, we unmark D >> click OK.
Hence, you can see Company D has been hidden.
In addition, you can see the sum visible cells with criteria in cell C13.
4. Use of SUMPRODUCT, SUBTOTAL, OFFSET, and ROW Functions
In this method, we will use the combination of SUMPRODUCT, SUBTOTAL, OFFSET, and ROW, functions to sum visible cells with criteria.
Here, we will find out the Profit based on the Green color code.
Steps:
- First, we will type the following formula in cell C13.
=SUMPRODUCT(SUBTOTAL(109,OFFSET(D5,ROW(Table137[Profit])-ROW(D5),)),--(Table137[Color Code]=C12))
Formula Breakdown
- ROW(Table137[Profit])-ROW(D5) → the ROW function yields the row number of a range.
- OFFSET(D5,ROW(Table137[Profit])-ROW(D5),)) → the OFFSET function returns a section from a data set with a specific height and a specific width, situated at a specific number of rows down and a specific number of columns right from a given cell reference.
- SUBTOTAL(109,OFFSET(D5,ROW(Table137[Profit])-ROW(D5),)) → the SUBTOTAL function yields the summation of the Profit
-  SUMPRODUCT(SUBTOTAL(109,OFFSET(D5,ROW(Table137[Profit])-ROW(D5),)),–(Table137[Color Code]=C12)) → the SUMPRODUCT function yields the summation of products of a range.
- Output: $3500
- Explanation: Here, $3500 is the sum of the Profit based on the color Green.
- Then, press ENTER.
Therefore, you can see the result in cell C13.
- At this point, we followed Step-1 of Method-1 to insert a Table.
Hence, you can see the Table.
- Furthermore, we will click on the drop-down arrow of the heading Company in cell B4.
- After that, we unmark B >> click OK.
Hence, you can see Company B has been hidden.
In addition, you can see the sum visible cells with criteria in cell C13.
5. Using Filter Feature and SUBTOTAL Function to Sum Visible Cells with Criteria
In this method, we will use the Filter feature along with the SUBTOTAL function to sum visible cells with criteria.
Steps:
- First, we add the Sales and Total columns into the dataset.
- Then, we select the entire dataset from cells B4:F10.
- After that, from the Data tab >> select Filter.
As a result, you can see the filter icon in the dataset.
- Furthermore, we will click on the drop-down arrow of the heading Color Code in cell C4.
- Then, we will unmark Green >> click OK.
Therefore, you can see the Filtered dataset with visible Red color only.
- Next, we will click we will click on the drop-down arrow of the heading Color Code in cell D4
- Next, we unmark $1300 >> click OK.
- Moreover, we type the following formula in cell F5.
=SUBTOTAL(9,D5:E5)Here, the SUBTOTAL function yields the summation of the Profit column.
- At this point, press ENTER.
As a result, you can see the result in cell F5.
- Furthermore, we will drag down the formula with the Fill Handle tool.
As a result, you can see the sum of the visible cells.
- Next, we will click we will click on the drop-down arrow of the heading Color Code in cell D4.
- Furthermore, we will mark $1300 >> click OK.
Then, you can see that the Total for Profit $1300 is not present in cell F9.
Therefore, you can find the sum visible cells with criteria.
Read More: Excel VBA to Select First Visible Cell in Filtered Range
Practice Section
You can download the above Excel file to practice the explained methods.
Conclusion
Here, we tried to show you 5 methods for Excel Sum Visible Cells with Criteria. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below. Please visit our website Exceldemy to explore more.
Related Articles
- How to Select Visible Cells in Excel with VBA (5 Easy Methods)
- [Fixed!] Paste Visible Cells Only Not Working (4 Possible Solutions)
- Excel VBA: Select Visible Cells After Autofilter (4 Examples)
- How to Count Only Visible Cells in Excel (5 Tricks)
- How to Copy and Paste Visible Cells Only in Excel (3 Easy Ways)
- How to Copy Visible Cells Only in Excel (4 Fast Ways)
