COUNTIF over multiple ranges

[Dave Punky]

Hi all,

I'm having a problem trying to COUNTIF several cells over multiple ranges. Basically all I want to do is count all of the cells greater than 0 but the ranges are not directly aligned.

The ranges are basically A1, A3, A5 (and so on) so I can't set the range as such.

I've tried naming the range and doing countif with that like so:

=COUNTIF(range1,">0")

I've also tried using COUNTA with a formula making the cell equal "" so it displays a blank value, but as the cells actually have contents it's not worked.

If there any way of doing a COUNTIF greater than 0 with a formula with each of the ranges. I have 11 in total in the same pattern as above. Any ideas / suggestions would be appreciated.

 

[Jonmo1]

You said 11 cells, A1, A3, A5 etc.
11 cells in that pattern is every other cell A1 through A21
Right?

Try

=SUMPRODUCT(--(A1:A21>0),--(MOD(ROW(A1:A21)-ROW(A1)+1,2)=1))

 

[Snakehips]

Dave Punky,
Can you just clarify?
Are your 1 ranges A1, A3, thro' to A11, ie single cells??? If so is there other data in the intermediate cells.

 

[Dave Punky]

@Snakehips,

To clarify, jonmo1's definition is correct. The ranges go:

A1, A3, A5, A7, A9, A11, A13, A15, A17, A19, A21

There is other data in the cells inbetween which cannot be counted.

@jonmo1

That works great! I noticed something similar online but didn't have any luck making it work. If I wanted to go horizontally for another set of values, I can just use this formula instead right?

=SUMPRODUCT(--(A1:U1>0),--(MOD(COLUMN(A1:U1)-ROW(A1)+1,2)=1))

Thank you both for replying!

 

[Jonmo1]

Glad to help, thanks for the feedback..

Almost, you have to change BOTH ROW references to Column.