Sum based on cell color in row

atuljadhavnetafim

Active Member
Joined
Apr 7, 2012
Messages
278
Hi ,

i have excel with multiple column, every column has background color, at last I need total based on color, if I change the color then I automatically added in total.
in below example.
Payment column has yellow color, so i need formula in J colum which get sum only where backround color yellow.
if i change colour of D2 as yellow then the total will be 270.
i can add it manually but i have so many column so need formula.
the column header name (first row) will not change.

1601388273712.png
 

Attachments

  • 1601388252184.png
    1601388252184.png
    12.8 KB · Views: 0

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,527
Office Version
  1. 365
Platform
  1. Windows
In col J do you want to sum all columns with Payment in row 1 regardless of colour?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,527
Office Version
  1. 365
Platform
  1. Windows
Ok in J2 copied down
Excel Formula:
=SUMIFS(C2:H2,$C$1:$H$1,"Payment")
and similar for k2 & L2.
Excel formulae cannot see colour or formatting
 

atuljadhavnetafim

Active Member
Joined
Apr 7, 2012
Messages
278
Ok in J2 copied down
Excel Formula:
=SUMIFS(C2:H2,$C$1:$H$1,"Payment")
and similar for k2 & L2.
Excel formulae cannot see colour or formatting
that's good enough, thanks, I am using sumif and sumifs but not this way.
thanks again
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,527
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,076
Messages
5,570,070
Members
412,310
Latest member
mark884
Top