Counting Zeros in every other column...

[saxokid]

Baffled me for hours now......

Basically I am managing a fantasy football league and need to count clean sheets using a grid of results.

So in effect I need to count the zero's in a row but skipping a column each time.

So count A1, C1, E1 and so on, pulling what little hair I have left out over this one, probably something very simple that I am not getting..

Anyone?

 

[Jonmo1]

Do you really need to count every other cell?
Is it possible for a 0 (blanks are OK) to appear in B1 or D1 ?

 

[Jonmo1]

If 0's will NOT appear in the intermediate cells (B1, D1, F1, etc) then a simple countif works

=COUNTIF(A1:G1,0)

Otherwise, try
=SUMPRODUCT(--(A1:J1=0),--(ISNUMBER(A1:J1)),--(MOD(COLUMN(A1:J1),2)=1))

This is counting the ODD #d columns (A C D)
If you wanted to count the EVEN columns (B D F)
Then change the =1 to =0

 

[saxokid]

well im counting football results, the results

I need to count the zeros in every other cell to determine clean sheets for each team.. if that makes sense.

The data is in a grid format, vertically I can do it because the results are on top of each other, horizontally they are next to each other so I need to ignore the first number and count the next if its a zero..

Really hope that makes sense.

 

[saxokid]

The reason I am skipping a column is that if the result is 0-0 I only want to count one of the zeros or it will count as if the team I am looking at has had two clean sheets.

 

[Jonmo1]

The reason I am skipping a column is that if the result is 0-0 I only want to count one of the zeros or it will count as if the team I am looking at has had two clean sheets.

So that's a Yes then, a 0 might appear in the intermediate cells B1 D1

Try the sumproduct above.

 

[saxokid]

You my friend are my hero, completely understand what you have done and its worked first time!

Thanks so much!

 

[Jonmo1]

Glad to help, thanks for the feedback.