Using Conditional Sum to check if a cell if highlighted.

[bdpitta]

Hello,

I have an Excel spreadsheet that contains a list of users and then scores for each section of a test. If a user does not complete a section of the test, they are assigned a value equal to the lowest score from the group that did finish the section. For example, if 50 of the 75 people finish the section, and the lowest score earned is 42, then the 25 people who did not finish the section are assigned a 42. When I input the scores for the people who did not finish, I highlight the cell, so that I know it was an assigned score and not an earned score.

When I give each user a total, I would like a way to give them not only a total score, but also a score that only counts earned scores and not assigned scores.

Does anyone have an idea of how I could write a SumIf formula that only counts unhighlighted cells?

Thank you!

 

[DaleWatson123321]

Hi bdpitta,

Without using VBA, it's not easy to capture the Color value of a cell. So here's an approach I would suggest...

Use Conditional Formatting to color your cell containing the score, and have the Conditional Formatting reference an adjacent column.

You can keep this adjacent column "out of view" by making the column width very small, and by formatting the entire column with a WHITE font.

For my example, let's say that your score column is "B", and your adjacent column (for identifying assigned scores) is column "C".

After both columns are formatted, then all you would do to both a) identify a score as an assigned score, and b) color the score... is to enter (for example) the letter "A" in column "C" opposite the score you just entered in column "B".

To set up the formatting, you can follow these steps:

1) For the column containing the scores ("B&quot, click on the entire column - i.e. click on the Column Letter.

2) Activate Conditional Formatting... menu: Format - Conditional Formatting.

3) Make Condition 1: "Formula Is" =C1="A"

4) Click "Format" and under "Patterns", choose a color, and then "OK" twice to finish.

5) Format column "C" by clicking on the column letter, and then use the Font Color icon to paint the Font Color WHITE.

6) Adjust the width of the column "C", perhaps to something like: .5

You're now done with the formatting. To test it out, enter "A" in column "C" opposite any number. The result will be that the background color of the number will change to the color you specified in the Conditional Formatting.


SUMIF formula... Here's an example of what you can use as your SUMIF formula. This will ONLY add those numbers that do not have a value in column "C". Therefore those scores identified with an "A" will NOT be added.

=SUMIF(C4:C11,"",B4:B11)

You'll naturally need to adjust the ranges to fit with the actual ranges of the scores you want totalled.

I hope this helps. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca