Sumif based on conditional format
Posted by Uttam on May 21, 2001 12:56 PM
Hello,
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
and
=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
Uttam,
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)
Aladin
Posted by Uttam on May 21, 2001 2:23 PM
Thank you both - both solutions work perfectly.
Warm regards,
Uttam
------------
