Can I use color as criteria for sumif?

[E.J.]

I have a worksheet where competitors have up to 8 scores in a row, I have manually highlighted 3 of the scores (by changing the font to red), which I would like automatically totalled down a colomn. Can anyone help please?

[Colin Legg]

Welcome to the forum.

No, SUMIF() can't do that by itself - you would have to write a VBA UDF to do this - and even that would not be a very good solution.

If you tell us the reasoning you used to highlight 3 of the scores (eg. they were the 3 largest scores) then we might be able to come up with something for you? A sample workbook might also help...

[E.J.]

Thanks Colin, The scores aren't always the best 3. They have to be from different venues. Is there a different way that I can highlight them to get my desired outcome? The attached Sheet shows how there must be at least one score from each venue. Thanks

[Colin Legg]

Okay, so for each shooter, it's

(1) The highest score from WAGC
+
(2) The highest score from CMT
+
(3) The larger of the second highest score from either venue


Is that right?

[E.J.]

Thats right

[Colin Legg]

Okay, I've attached a solution for you. I added three more columns to make it easier to follow.

To get the highest score for each shooter at the WAGC I used:

Please Login or Register  to view this content.

To get the highest score for each shooter at the CMT I used:

Please Login or Register  to view this content.

To get the larger of the 2nd highest score at either venue I used:

Please Login or Register  to view this content.

In the spreadsheet, you'll notice that in the formula bar the forumlas are surrounded by { }. This means that they are array formulas. To achieve this effect, when you type the formula into the formula bar, you have to complete the entry with CTRL+SHIFT+ENTER, not just ENTER, and Excel will automatically put the { } into the formula bar for you. You can't type the { } in yourself.

[E.J.]

Wow, that looks great. It will take me a while to work out your formulaes. Thankyou very much