COUNTIF over multiple ranges

Dave Punky

Board Regular
Joined
Jan 7, 2010
Messages
133
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.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
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

Board Regular
Joined
Jan 7, 2010
Messages
133
@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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Glad to help, thanks for the feedback..

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

Watch MrExcel Video

Forum statistics

Threads
1,122,662
Messages
5,597,440
Members
414,143
Latest member
CDLAegis

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top