Sum of a column by cell color?

jhnmjr

Not sure if this is possible or not. I was looking into conditional formatting, but didn't seem to find anything that would work.

I simply want to get the sum of a column based on the color of the cell in the column. In other words, I only want to see the sum of red cells and exclude green cells.

If this is not possible, perhaps there is another way to accomplish my task. I am tracking orders that need to be fulfilled. Each item has it's own column and each order is listed in rows. Currently, new orders listed in the sheet are assigned a red cell color. Once they are fulfilled, they are turned to green. I was to quickly see a total at the bottom of the sheet to see how many of each item needs to still be fulfilled and I don't want to simply delete the order as I need to retain the data. Any ideas on how to accomplish this?

Thanks in advance for any help!

-John 

Mike Wilday

Hi John, 

Smartsheets doesn't have the ability to count cell color or any conditional formatting, but if you have triggers for the conditional formatting, like the status being set to "fulfilled", "New Order" then you could count the number of cells in that column that have that status.

=Countif([Status Column Name]:[Status Column Name], "Fulfilled") Will give you a count of all of the times that the word "Fulfilled" appears in the column with Status Column Name. 



You'll want to put that formula in a different column to make sure that you don't create a circular reference. Just replace Status Column Name with your own column name. And "Fulfilled" with your own status. 

https://help.smartsheet.com/function/countif

Dan Davis

John,  Mikes suggestion is a good one, I have utilized this strategy and it has worked well for me.  I have also added columns, off to the right so they are not mixed in directly with the main collection of data, and I have used entries in those columns to generate reports.  For example, I have manually applied  numbers 1, 2, 3, and 4 to corresponding indent levels (parent-child relationships).  I can run reports to show data on specific indent levels, or I can filter sheets by indent levels to review or edit unique formatting and formulas that occur in each indent level.