Conditional Formatting

[brett4098]

I have the normal three sheet workbook open. I was wondering if on one page i fill (color) a certain cell and could have, on another page, a formula summing the number of cells that i've highlighted in a certain color?

 

[Joe4]

See http://www.cpearson.com/excel/colors.htm

 

[Albert 1]

That question has been here many, many times.
E.g. here (but there are many others, try the search-function of this site).
http://www.mrexcel.com/board2/viewtopic.php?t=63449&highlight=sum+color+colour

 

[brett4098]

What about if i put an X in a cell instead of shading it, would it calculate it then?

 

[Joe4]

Either way should work, using the shading or using a Flag like "X". If you wanted to SUM all the values in the first 100 rows on Sheet 1, column A depending on if there is an X in column B, then use:

=SUMIF(Sheet1!B1:B100,"X",Sheet1!A1:A100)

 

[brett4098]

I am having a problem with the last part of that formula. What is that exactly?

 

[Joe4]

Look at Excel help on the SUMIF function. It is a great resource, complete with descriptions and examples.

This is taken directly from there:

SUMIF(range,criteria,sum_range)

Range is the range of cells you want evaluated.

Criteria is the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, "32", ">32", "apples".

Sum_range are the actual cells to sum.

Note that the first range is the range that contains the X's. The range of values you want to sum is the 3rd argument (Sum_range).

 

[brett4098]

It doesn't update the formula until I save the workbook. Any suggestions? This is my formula--=SUMIF('2004 CALENDAR'!A5:AE5,"X",'2004 CALENDAR'!A6:AE6). In other words, if I add an X, it won't change the sum unless I save it.

 

[Joe4]

It sounds like your calculation setting is set to Manual. Go to the Tools drop down menu and select Options. Go to the Calculation tab and make sure the "Automatic" radio button is selected.

 

[brett4098]

That was it! Thank you so very much!