COUNTIF with noncontiuous range

L

lurker

New Member
Joined
Feb 21, 2003
Messages
1
I want to use the countif function with a non-continuous range but it does not seem to work. For example:

I name a range foobar that is (c3:c10,c12:c14)

now I want to use =countif(foobar,TRUE)

no Workee...Apparently not all ranges are created equal...

it works if I use =countif(c3:c10,TRUE)+countif(c12:c14,TRUE)

but I want to just use a single range definition that contains a noncontinuous range...

Does anyone have any ideas?
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
That is smokin' Juan!

I was going to say throw all of your cells in a named range, MyRange, and trying an array formula like the last half of an array formula I posted here:

http://www.mrexcel.com/board/viewtopic.php?topic=40862&forum=2

But Juan's is much better! :wink:

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2003-02-22 20:03
 
Upvote 0
Thanks Nate,

What I'm looking at now is a way to use only ONE named range, and use a combination of INDEX and AREAS to get COUNTIF to work with one multiple named range. I hope that works, someday !
 
Upvote 0
Juan Pablo G. said:
Thanks Nate,

What I'm looking at now is a way to use only ONE named range, and use a combination of INDEX and AREAS to get COUNTIF to work with one multiple named range. I hope that works, someday !
Click to expand...

I'm not sure what Nate has in mind, but Hager's formula can indeed be modified to do a conditional count in a range consisting of multiple areas...

{=SUM(N(LARGE(Rng,ROW(INDIRECT("1:"&COUNT(Rng))))=1))}

or, with SumProduct...

=SUMRODUCT(N(LARGE(Rng,ROW(INDIRECT("1:"&COUNT(Rng))))=1))

These count 1's in Rng.

Juan: Hager's is admittedly a generic approach to tackle with the non-contiguous or multiple area ranges.

Aladin
 
Upvote 0
Hello,

Aladin Akyurek said:
I'm not sure what Nate has in mind, but Hager's formula can indeed be modified to do a conditional count in a range consisting of multiple areas...

{=SUM(N(LARGE(Rng,ROW(INDIRECT("1:"&COUNT(Rng))))=1))}

Aladin
Click to expand...

Yes indeed, that's precisely what I meant, the denominator of my conditional averaging formula, this is the Hager array, I just added a numerator to create the averaging, by request.

Looks like the link is broken. Here it is.

Nicely done Aladin. :wink:
 
Upvote 0
You must log in or register to reply here.

Similar threads

Dexir
  • Solved
Countifs query
Replies
3
Views
194
Dexir
Dexir
Ramballah
  • Question
Huge nested IF or is there a simpler way?
Replies
4
Views
214
Ramballah
Ramballah
M
  • Solved
IF formula is used in Macro, Get If ranges value from Cell.
Replies
3
Views
119
Joe4
Joe4
L
  • Question
Check for any match between three ranges but ignore zeros
Replies
4
Views
187
Louis chiron
L
G
  • Solved
Search a variable range based on cell address
Replies
4
Views
144
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,429
Messages
6,124,834
Members
449,192
Latest member
mcgeeaudrey

Share this page

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
Back
Top