• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

  • Test .XLSX
    10.4 KB · Views: 7
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.
 
I understood that a macro-enabled workbook was not possible - apologies if I misunderstood.
 
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 :(
 
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

68169

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


Regards
Bosco
 

Attachments

  • SumColor (BY).xlsm
    10.9 KB · Views: 11
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 :)
 
KNMSRK
You should reread Forum Rules:
especially How to get the Best Results at Chandoo.org
and notice Please post, new posts in the correct forums, not as Emails/Messages to people
This is ... Ask an Excel Question- Forum
 
KNMSRK
You should reread Forum Rules:
especially How to get the Best Results at Chandoo.org
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.
 
Back
Top