MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sumif based on conditional format

Posted by Uttam on May 21, 2001 12:56 PM


Consider the following worksheet with the following values:

A1 = "Actual"
B1 = "Projected"
C1 = "Projected"
A2 = 10,000
B2 = 20,100
C2 = 30,200

Row 2 has conditional formatting - when the content of Row 1 in the same column has "Actual" the text is in Green. If it is "Projected" the text is in Red.

Column D is my Actual Totals Column and Column E is my Projected Totals Column.

Hence, in Cell D2 I would like to sum up cells (A2:C2) which have Green text.

And in Cell E2, the sum will be of Red Text.

Most important: if I change any of the A1, B1 or C1 from "Actual" to "Projected" and vice versa, I would like Totals in D2 and E2 to change appropriately.

Any help deeply appreciated.

Thank you.

Posted by IML on May 21, 2001 1:33 PM

Forget about the conditional formats and just use the same criteria in a sumif statement. For instance, put Actual in D1 and Projected in e1 and use
=SUMIF($A1:$C1,D1,$A2:$C2) in D2
=SUMIF($A1:$C1,E1,$A2:$C2) in e2

I think that will get you what you are looking for.

Good luck

Posted by Aladin Akyurek on May 21, 2001 1:34 PM

Re: Sumif based tout court


Why not use the following simple formulas?

In D2 enter: =SUMIF(A1:C1,"Actual",A2:C2)

In E2 enter: =SUMIF(A1:C1,"Projected",A2:C2)


Posted by Uttam on May 21, 2001 2:23 PM

Thank you both - both solutions work perfectly.

Warm regards,