row value sum according to header color

[ushaanu]

hi all,

I have some data in excel file which is having different color header , I need when I change header cell color in red , only red colored row data sum in one cell in file .

thanks in advance

sample file

 

[vletm]

ushaanu
I need when I change header cell color in red
Above could be a challenge ... to get something to calculate if change a color.
There should be some kind of other action which could make it possible.

 

[6StringJazzer]

Note that vletm's solution uses VBA. There is no other way to do this.

Complicating matters is that there is no VBA event that fires when you change the format of a cell. So you might have to make the function volatile or force recalculation when the colors are changed.

 

[bosco_yip]

Note that vletm's solution uses VBA. There is no other way to do this.

Complicating matters is that there is no VBA event that fires when you change the format of a cell. So you might have to make the function volatile or force recalculation when the colors are changed.

Sum red color header columns can be done by formula, please see,

1] Create a range name by :

Select Define Name >>

• Name : SumRedHeaderCol
• Refers to : =SUMPRODUCT((GET.CELL(63,IF(1,+OFFSET($A$1:$J$1,,COLUMN($A$1:$J$1)-MIN(COLUMN($A$1:$J$1)),)))=GET.CELL(63,$M$1))*$A$2:$J$12)

Then,

2] In M3, enter formula :

=SumRedHeaderCol

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

 

[p45cal]

Another non-vba way attached.