Have you ever tried to Sum data values based on cell color, but without any success? Perhaps you have allocated colors to values representing cost centers and would like to sum based on the cost centers. Well, by using the Subtotal function and the Data Filter option one can sum data values based on cell color. Follow the steps below as explain how that can be done.
Applies To: Excel 2003, 2007, and 2010
- With reference to the screen shot above select cell C23
- Enter the formula as; =SUBTOTAL (9, C4:C22)
a. 9 = Sum, 1 =Average, 4 =Max, 5 = Min, 2=Count
b. C4:C22 represents the data range - Select any cell within the data list
- Select the Data tab, and then Filter under the Sort & Filter group
- Select as per screen shot below
6. The result will be that the sum for the cost centre-A (Green) will be displayed
7. To display the sum for other cost centres, repeat step 5 and select other colors
If you have any suggestions for an Excel Tips & Tricks topic, or you would like help with a particular function, please email enablement@alchemex.com and yours could be the next Tip of the Week.
Alchemex provides examples of MS Excel procedures for illustration only, without warranty expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The MS Excel procedures on this web site are provided “as is” cannot be guaranteed that they can be used in all situations
