# Microsoft Office Application Help - Excel Help forum > For Other Platforms(Mac, Google Docs, Mobile OS etc) >  > [SOLVED] Countif or Sumif based on Conditional Formatting Color

## Kune

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!!

----------


## dwint

in H4

=COUNTIF(E4:G4,1)

copy down

in H22

=MAX(H4:H14)

----------


## Kune

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

----------


## dwint

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.

----------


## Kune

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.

----------


## Moo the Dog

Why not try something a bit more simple if all you are trying to do is figure out who had the most winning picks each week... I have attached your original file, with a second sheet set up a bit differently. Hope it solves your issue.

- Vince

----------


## Kune

Hey Vince,

Actually that's very helpful! Thank you!

I used your logic for how to show who picked which team and I've totaled the number of people that picked the winning team at the bottom. Now I'm attempting to sumproduct a row.

Check this out.

Sample-Modified.xlsx

----------


## Moo the Dog

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:



```
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

----------

