Countif or Sumif based on Conditional Formatting Color

Hey everybody,

I need a way to count the number of boxes in a row that have a fill of red(conditional formatting).

I've done some research and have found that I need to use the same logic that created the fill if I want to count that box with countif. My only problem is that the cell fill is based on two conditions.

Condition 1) Cell Value is, =0, No Format Set

Condition 2) Formula is, =AND($Y$54<$X$54), Format Fill Red

I've added a sample.xslx file to help describe what I'm looking for.

Thanks!!

in H4

=COUNTIF(E4:G4,1)

copy down

in H22

=MAX(H4:H14)

Hi dwint,

Thanks for replying.

Question: Wouldn't that formula also count the teams that particular person (row) has picked for the loosing team?

I use 1 to denote which team each person has chosen to win the game. It is my intention for these fields to be filled out prior to applying the game score so I don't have to do any work in deciding who won the pool for the week other than entering game scores. =)

Again, thanks for replying to my post.

Kune

basickly, i dont know the rule of your game and i dont know what does the meaning of "Atl, Dal, TB, and SD", because it might be never played in my country

I just post the answer based on your logic which you have written in your sample.

Atl Dal TB all refer to american football teams.

That has no significance with what I intend. I am just looking for a way to count only those cells that are colored red.

So, if a person picks the winning team that cell will turn red. At the end of it all, I need to know who picked the most winning teams.

I hope that helps.

Kune,

In your updated example, column 'J' was returning the wrong total due to blank cells in the span (D6:I6) - it's best not to use merged or 'span across multiple column' cells in formulas, since output formulas that rely on those cells have to be unnecessarily adjusted.

Regardless, I have adjusted the formula in cell J6 to:

Formula:

Please Login or Register  to view this content.

You can paste that into cell J6, then fill down. It should give you the correct totals then. Basically, it is the same SUMPRODUCT formula you had, except I added a COUNTBLANK operator to deduct the number of blank cells in the span.

Also... be sure you use the proper reference models in your formulas. ex: Use (D6:I6=$D$4:$I$4) instead of (D6:I6=D4:I4) since you want the reference row (winning team) to remain constant.

Glad I could help.

- Vince