We help IT Professionals succeed at work.

excel sum dependant on conditional formatting

PeterBaileyUk
on
How do I sum a row just where the month is highlighted red?
ie it needs to track the highlight
ee-ex-2.xlsx
Comment
Watch Question

Most Valuable Expert 2013

Commented:
Your conditional formatting is based on row 11 value being < 80 so you can sum on the same basis, e.g. for row 7

=SUMIF(C$11:M$11,"<80",C7:M7)

copy down

regards, barry

Commented:
try this with an additionl row of switches. set as 1 to total
ee-ex-2.xlsx

Author

Commented:
@barryhoudini:

I am summing row 9 which manually using autosum (just to check) gives

124      0      205      362      430      0      251      443 = 1815 (each corresponding row 11 is <80

=SUMIF(C$11:L$11,"<80",C8:L8) (if it functions how i think it does) returns 1740 which is not as expected.

Most Valuable Expert 2013
Commented:
Row 8 is 1740, so for row 9 it needs to be

=SUMIF(C$11:L$11,"<80",C9:L9)

which gives me 1815 as per your calculations

regards, barry

Author

Commented:
thank you I chose this method as it negated the need for switches. thank you.