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

row value sum according to header color

ushaanu

Member
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
 

Attachments

  • sample.xlsx
    9.6 KB · Views: 5
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.
 

Attachments

  • sample.xlsb
    16.3 KB · Views: 4
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.
 
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.

78395
 

Attachments

  • SumRedHeaderCol.xlsm
    11.9 KB · Views: 6
Another non-vba way attached.
 

Attachments

  • Chandoo47848sample.xlsx
    22.4 KB · Views: 6
Back
Top