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".
derrobert1970Asked:
Who is Participating?
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.

Justin AlvarezAccount 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.
1
Koen Van WielinkBusiness Intelligence SpecialistCommented:
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:

https://chrome.google.com/webstore/detail/sort-by-color/ljeafnadccaobiifhflfhgmbfmdhmanp?utm_source=permalink

https://chrome.google.com/webstore/detail/colorarranger/peamiedkpabiagflbceioliielpfehpb?utm_source=plus

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.
1
derrobert1970Author 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.

Thank you in advance
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Koen Van WielinkBusiness Intelligence SpecialistCommented:
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").
1

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
derrobert1970Author Commented:
Hi Koen,

Your comment referenced a sample sheet, but I do not see that sample.
0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
Apologies Robert, in my rush I forgot to add the link (was getting ready to leave the office...).
Here's the link:

https://drive.google.com/open?id=1NiiliyIEPDxM8mbdNwqq7EWsEB-qcck7XvTMsi5GUtU

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

Accepted answer: 125 points for Koen Van Wielink's comment #a40911222
Assisted answer: 125 points for justin_alvarez's comment #a40908974
Assisted answer: 125 points for Koen Van Wielink's comment #a40910938
Assisted answer: 0 points for derrobert1970's comment #a40911049
Assisted answer: 0 points for derrobert1970's comment #a40911403
Assisted answer: 125 points for Koen Van Wielink's comment #a40911512

for the following reason:

The solution provided is spot-on and solves the problem.
0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
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.
1
derrobert1970Author 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.
0
derrobert1970Author Commented:
Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Google Apps

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.