How to Sum Visible Cells with Criteria in Excel (5 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel Sum Visible Cells with Criteria


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.

Inserted Table to Excel Sum Visible Cells with Criteria

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.

Using SUBTOTAL Function to Excel Sum Visible Cells with Criteria

  • 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.

Dataset with Green Color to Sum Visible Cells with Criteria in Excel


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.

Applying Aggregate Function to Sum Visible Cells with Criteria

  • 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.

Use of SUMIF Function to Sum Visible Cells with Criteria in Excel

  • 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.

Showing Profit Based on Red color to Excel Sum Visible Cells with Criteria


Similar Readings


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

Applying Combined Functions to Sum Visible Cells with Criteria in Excel

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

Excel Sum Visible Cells with Criteria using Multiple Functions

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.

Inserting Filter Feature to Sum Visible Cells with Criteria in Excel

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.

Employing SUBTOTAL Function to Sum Visible Cells with Criteria in Excel

  • 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.

Applying Filter feature along with Formula to Excel 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

Afia Kona

Afia Kona

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo