How to Count or Sum Cells With Specific Color in Excel

preview_player
Показать описание
*How to Count or Sum Cells with Specific Colour in Excel*

Now without further ado lets learn how to count or sum cells with specific colour in excel using formula. Let’s prepare the formula to sum cells by colour in excel

*Summing Cells with Background Colour:*

1. *Use SUBTOTAL Function for Sum:*
In a separate cell, use the SUBTOTAL function to sum the filtered cells. For example, if your data is in column B, and you want to sum the filtered cells, you can use a formula like this:
=SUBTOTAL(9,B:B)
The number “9” represents the SUM function, and “B:B” is the column containing your data.

2. *Filter Your Data:*
Apply a filter to your dataset. Click on the column header of the coloured cells. Under the home tab go to sort and filter and then click on filter

3. *Sort Your Data:*
- Sort your data based on the coloured cells. Click on the filter button you just created. Now click on “Filter by colour” and select the colour you want to filter with. Once you filter out the colour your formula will show total of that filtered cell only. Meaning you can now sum coloured cells in excel without vba.

*Counting Cells with Background Color:*
1. *Use SUBTOTAL Function to count cells with specific background colour:*
In a separate cell, use the SUBTOTAL function to count the filtered cells. For example, if your data is in column B, and you want to count the filtered cells, you can use a formula like this:
=SUBTOTAL(103,B:B)
The number “103” represents the count function, and “B:B” is the column containing your data.

2. *Filter Your Data:*
Apply a filter to your dataset. Click on the column header of the coloured cells. Under the home tab go to sort and filter and then click on filter

3. *Sort Your Data:*
- Sort your data based on the coloured cells. Click on the filter button you just created. Now click on “Filter by colour” and select the colour you want to filter with. Once you filter out the colour your formula will show total of that filtered cell only. Meaning you can now count coloured cells in excel without vba.

#Count #Sum #Filter #backgroundcolour

Thanks for watching.
----------------------------------------------------------------------------------------
Support the channel with as low as $5
----------------------------------------------------------------------------------------
Please subscribe to #excel10tutorial

Here goes the most recent video of the channel:

Playlists:

Social media:
Рекомендации по теме
Комментарии
Автор

Hello brother
thank you for this helpful videos but in that same topic i want solution of the counting of colored cells, now here is changed, the cells are colored with Conditional formatting in red & green format. so please provide solution to count conditional format colored cells number?
please provide solution ASAP it very much helpful for me thank you in advance.

sauravmaheta