How to sum the column based cell color

KNMSRK

New Member
Hi,

Please help me to sum up the column based on the color in other column. In the attached file Column S would be in Three colors based on the parameters Met(In Green color),Partially Met(In Amber color), Not Met(In red Color).

Requirement is

We need to sum up the column M based on the colors in Column S and update the Cells F5,(Green- Met) Cell G5(Amber - partially Met), and H5(Red - not Met)

I have tried finding out the code of the color and sum up the M column, however it is not working as expected

Find attached the file and help

Note: problem should be solved with an excel formula but not with a VBA code

Attachments

• 10.4 KB Views: 5

AliGW

Active Member
You cannot sum on colour using formulae - sorry.

Fluff13

Active Member
Goto Name manager, New, in name put GetColour, in Refers to put =GET.CELL(63,INDIRECT("rc[-1]",FALSE)), OK.
In T5 filled down put =GetColour
Then you can use Sumif looking at column T.

The workbook will need to be saved as macro enabled.

AliGW

Active Member
I understood that a macro-enabled workbook was not possible - apologies if I misunderstood.

Fluff13

Active Member
If you're right, then I agree, it can't be done.

KNMSRK

New Member
Goto Name manager, New, in name put GetColour, in Refers to put =GET.CELL(63,INDIRECT("rc[-1]",FALSE)), OK.
In T5 filled down put =GetColour
Then you can use Sumif looking at column T.

The workbook will need to be saved as macro enabled.
Hi,
No luck with the above formula

bosco_yip

Excel Ninja
Formula solution.

1] Copy cell background color to criteria header in >> F4 with green, G4 with Amber and H4 with Red

2] Create a range name

2.1. Select F5 >> Formulas >> Define Name >>

2.2. Name : Sumcolor

2.3. Refers to : =SUMPRODUCT((GET.CELL(63,IF(1,+OFFSET(\$S\$5:\$S\$9,ROW(\$S\$5:\$S\$9)-MIN(ROW(\$S\$5:\$S\$9)),)))=GET.CELL(63,F\$4))*\$M\$5:\$M\$9)

2.4. OK >> Finish

Then

3] In F5, formula copied across right to H5

=SumColor

Remark : GetCell is a Macro 4 function file need to be saved in xlsm type Excel Macro-Enabled Worksheet

Regards
Bosco

Attachments

• 10.9 KB Views: 7

AliGW

Active Member
Hi,
No luck with the above formula
Can you just clarifly whether a macro-enabled file is acceptable to you or not?

KNMSRK

New Member
Formula solution.

1] Copy cell background color to criteria header in >> F4 with green, G4 with Amber and H4 with Red

2] Create a range name

2.1. Select F5 >> Formulas >> Define Name >>

2.2. Name : Sumcolor

2.3. Refers to : =SUMPRODUCT((GET.CELL(63,IF(1,+OFFSET(\$S\$5:\$S\$9,ROW(\$S\$5:\$S\$9)-MIN(ROW(\$S\$5:\$S\$9)),)))=GET.CELL(63,F\$4))*\$M\$5:\$M\$9)

2.4. OK >> Finish

Then

3] In F5, formula copied across right to H5

=SumColor

View attachment 68169

Remark : GetCell is a Macro 4 function file need to be saved in xlsm type Excel Macro-Enabled Worksheet

Regards
Bosco
Hi Bosco,

Thank you so much !!
It is working

vletm

Excel Ninja
KNMSRK
and notice Please post, new posts in the correct forums, not as Emails/Messages to people
This is ... Ask an Excel Question- Forum

KNMSRK

New Member
KNMSRK
and notice Please post, new posts in the correct forums, not as Emails/Messages to people
This is ... Ask an Excel Question- Forum
Hi

Apologies, I am a New Member to this forum, will follow the rules going forward.

Monty

Well-Known Member
Thanks Both.

This is exactly what I was searching for..