Using IF formulas

Mar 7th, 2002, 02:29 PM

I have a worksheet with some information in cells with a "blue" fill color and other cells in a "green" fill color. All numbers are eventually going to be in "blue". I would like to develop a formula that will calculate the total of the "blue" cells and continue to automatically calculate each time i change the cell colors. Any ideas will be appreciated.

Mark W. · Mar 7th, 2002, 05:01 PM

Suppose cells A1:A5 contain {10;3;5;6;7} and (B1,B3:B4) contain 1.

With cells A1:A5 selected and A1 as the active cell you could apply the following Conditional Format...

1st Condition: Formula Is using =B1 formatting Blue cell pattern
2nd Condition: Formula Is using =TRUE formatting Green cell pattern

You can now toggle the background color by setting B1:B5 to 1 or blank. Column B can be hidden if desired.

...And, the following formula will produced the desired sum...

=SUMIF(B1:B5,1,A1:A5)

[ This Message was edited by: Mark W. on 2002-03-07 16:02 ]

Chris Davison · Mar 8th, 2002, 01:19 PM

[ This Message was edited by: Chris Davison on 2002-03-09 01:23 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 7th, 2002, 02:29 PM	#1
Guest Posts: n/a	I have a worksheet with some information in cells with a "blue" fill color and other cells in a "green" fill color. All numbers are eventually going to be in "blue". I would like to develop a formula that will calculate the total of the "blue" cells and continue to automatically calculate each time i change the cell colors. Any ideas will be appreciated.

Mar 7th, 2002, 05:01 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Suppose cells A1:A5 contain {10;3;5;6;7} and (B1,B3:B4) contain 1. With cells A1:A5 selected and A1 as the active cell you could apply the following Conditional Format... 1st Condition: Formula Is using =B1 formatting Blue cell pattern 2nd Condition: Formula Is using =TRUE formatting Green cell pattern You can now toggle the background color by setting B1:B5 to 1 or blank. Column B can be hidden if desired. ...And, the following formula will produced the desired sum... =SUMIF(B1:B5,1,A1:A5) [ This Message was edited by: Mark W. on 2002-03-07 16:02 ]

Mar 8th, 2002, 01:19 PM	#3
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	[ This Message was edited by: Chris Davison on 2002-03-09 01:23 ]