# COUNTIF over multiple ranges

#### Dave Punky

##### Board Regular
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:
Code:
``=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.

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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))

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.

@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))

Glad to help, thanks for the feedback..

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

Replies
3
Views
113
Replies
4
Views
139
Replies
5
Views
200
Replies
2
Views
216
Replies
1
Views
151

1,203,072
Messages
6,053,377
Members
444,659
Latest member
vuphihung

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back