How to Count or Sum Cells With Specific Color in Excel
How to Count or Sum Cells with Specific Colour in Excel In this intermediate excel tutorial, I’ll explain a simple trick to count or sum cells with specific colour in excel without VBA. I’ll be using filter and Subtotal function to solve this. I’ve covered the same topics in the last video where I’ve showed a procedure using vba, if you are interested in that video here is it: • How to Count or Sum Cells With Specific Co... 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 / excel10tutorial ---------------------------------------------------------------------------------------- Please subscribe to #excel10tutorial https://goo.gl/uL8fqQ Here goes the most recent video of the channel: https://bit.ly/2UngIwS Playlists: Excel Tutorial for Beginners: https://goo.gl/UDrDcA Intermediate Excel Tutorial: https://tinyurl.com/59a837py Advance Excel Tutorial: https://goo.gl/ExYy7v Excel Text Case Conversion Techniques: https://goo.gl/xiP3tv Combine Workbook & Worksheets: https://bit.ly/2Tpf7DB All About Comments in Excel: https://bit.ly/excelcomments Excel VBA Programming Course: http://bit.ly/excelvbacourse ChatGPT Excel Mastermind: https://tinyurl.com/46kn7tmd Social media: Facebook: / excel10tutorial Twitter: / excel10tutorial Blogger: https://excel10tutorial.blogspot.com Tumblr: / excel10tutorial Instagram: / excel_10_tutorial Hubpages: https://hubpages.com/@excel10tutorial Quora: https://bit.ly/3bxB8JG Website: https://msexceltutorial.com/ Become a member and enjoy exclusive perks while supporting the channel you love! / @excel10tutorial
