# Color-Based Formula

I am using Google Sheets. I need to create a formula in a field that totals up the values of all fields in a given column, but only if the fields in the referenced column are of a certain "fill color".
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Account ManagerCommented:
Unfortunately, there is no built-in way to create a formula based on the background color of the cell. There may be an add-on or a custom script that can do this.

As an alternative, if you have conditional formatting that will automatically change the fill color you might consider using those conditions for the SUMIF formula.

For example, if you set a conditional format to assign the color yellow to any value over 100 then you have a known criteria. The SUMIF formula would become:

=SUMIF(range, ">100")

where the range could be something like A1:A100 (or whichever column you would like).

Let us know if that helps.
I don't know of any add-ons that can sum based on color, but I do know of 2 add-ons that can sort data based on cell color. Perhaps that's something you could use? Here are the links:

Alternatively you might be able to contact the developers of these add-ons and ask if they could modify the script to do what you are looking for. Chances are they already have the foundations working.
Author Commented:
I guess this is just not a doable strategy for Google Sheets. What I could use would be a precise formula for the following example:

1. I have a column of currency values that I wish to tally in a separate field.

2. For this example, the column of currency values is A1 through A Infinite (A1:A)

3. The formula would total all the values of each field in column A, providing two conditions are met:

Condition 1: The currency value in any given field in column A would be included in the tally, if the adjacent field (example B1 in the case of A1) contained a valid date or were not blank.

Condition 2: The currency value in any given field in column A would be included in the tally, if another adjacent field (example C1 in the case of A1) did NOT contain a specific alphanumeric string.

Because I am not completely helpless, this is what I have as a base formula:

=ARRAYFORMULA(A1:A)

But I do not know how to reference the adjacent fields in the proper way.

Sounds to me like Sumifs would fit your criteria. Have a look at this sample sheet:

The cell in E2 sums the values in column A only if the value in column B is larger than 0 (which conveniently appears to exclude invalid dates like in cell B7), and if the value in column C matches the string specified (in this example "CCC").

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Hi Koen,

Your comment referenced a sample sheet, but I do not see that sample.
Apologies Robert, in my rush I forgot to add the link (was getting ready to leave the office...).

Hope it helps.
Author Commented:
I've requested that this question be closed as follows:

Assisted answer: 125 points for justin_alvarez's comment #a40908974
Assisted answer: 0 points for derrobert1970's comment #a40911049
Assisted answer: 0 points for derrobert1970's comment #a40911403

for the following reason:

The solution provided is spot-on and solves the problem.
Hi Robert,

I think since you're accepting our answers you don't need to submit a close request. Simply accept multiple solutions as your answer to the question. A close request is intended for unsolved or redundant questions.
Author Commented:
I think since I am accepting the answers I don't need to submit a close request since I have since learned that a close request is intended for unsolved or redundant questions. I withdraw my request to close.
Author Commented:
Thank you