asked on

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".

SOLUTION

Justin Alvarez

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Koen Van Wielink

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

derrobert1970

ASKER

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

ASKER CERTIFIED SOLUTION

Koen Van Wielink

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

derrobert1970

ASKER

Hi Koen,

Your comment referenced a sample sheet, but I do not see that sample.

SOLUTION

Koen Van Wielink

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

derrobert1970

ASKER

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.

Koen Van Wielink

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.

derrobert1970

ASKER

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.

derrobert1970

ASKER

Thank you