Sum of a column by cell color?

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

Find more posts tagged with

Manufacturing

Formulas

Marketing

Conditional Formatting

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?