Count Same Number in three ranges

P

PaddyD

MrExcel MVP

Joined

May 1, 2002

Messages

14,234

• Nov 19, 2002

• #11

rank() is one of the functions that will cope with non-contiguous ranges. Highlight the cells of interest, give them an name (in the 'name box' to the left of the formula bar. use a normal rank() funciton:

=rank(F7,rankrange)

etc

paddy

 

Upvote 0

R

RJB

Board Regular

Joined

Jul 22, 2002

Messages

149

• Nov 19, 2002

• #12

Could you write out the complete formula the way that you would do it. I have tried what you have said and could not get the formula to work. Could be an error in the formula, in fact that was the message I kept receiving. Would it be something like this?
=RANK(F7,RankRange1,RankRange2,RankRange3,1)

 

Upvote 0

P

PaddyD

MrExcel MVP

Joined

May 1, 2002

Messages

14,234

• Nov 19, 2002

• #13

I did! You need a new name range that covers the three other ones, then just use the single named range...

 

Upvote 0

R

RJB

Board Regular

Joined

Jul 22, 2002

Messages

149

• Nov 19, 2002

• #14

Please look at the formula above. That is exactly the format I am using although I have named ranges of AP,BP, and CP. When I enter the formula it responds with "you have entered too many arguments". I am missing something small.

 

Upvote 0

P

PaddyD

MrExcel MVP

Joined

May 1, 2002

Messages

14,234

• Nov 19, 2002

• #15

I am missing something small.

Click to expand...

No...just what I said in the previous post!

"Highlight the cells of interest, give them an name (in the 'name box' to the left of the formula bar. use a normal rank() function"

So:

1) Select all the cells in the 3 distinct ranges (by holding down the control key as you do so)
2) Give these non-contiguous cells a name - eg "rankrange"
3) Use the name in the formula:

=rank(f7,rankrange,1)

 

Upvote 0

R

RJB

Board Regular

Joined

Jul 22, 2002

Messages

149

• Nov 20, 2002

• #16

Creating the ranges are not the problem. These named ranges have already been defined and used throughout the excel model, so I know they are OK. But when I put them in the RANK function, as so described, it gives me the "too many arguments" response. I do not get it.

 

Upvote 0

J

Juan Pablo González

MrExcel MVP

Joined

Feb 8, 2002

Messages

11,959

• Nov 20, 2002

• #17

That's because you ARE using too many arguments ! you need to combine all three named ranges into a big one, as Paddy has said. Then, you can use this "master" range in the RANK formula.

 

Upvote 0

J

Juan Pablo González

MrExcel MVP

Joined

Feb 8, 2002

Messages

11,959

• Nov 20, 2002

• #18

Ok, are you ready for this ? you CAN use COUNTIF with multiple ranges, non contigous, etc.

For example, I have this three ranges:

A2:D2
{1,2,3,2}

given the name Rng1

B4:G5
{5,6,7,8,9,10;2,3,4,5,6,7}

name: Rng2

A7:A9
{2;4;6}

name: Rng3

Now, say I want to count the number of 2s in the three ranges. I can use this simple formula:

=SUM(COUNTIF(INDIRECT({"Rng1","Rng2","Rng3"}),2))

it's not even array entered !

Here's the Excel model (Yellow range = Rng1, Green = Rng2, Blue = Rng3)

you can also use this approach for non named ranges, i.e.:

=SUM(COUNTIF(INDIRECT({"A2:D2","B4:G5","A7:A9"}),2))

Book1
	A	B	C	D	E	F	G
1
2	1	2	3	2
3
4		5	6	7	8	9	10
5		2	3	4	5	6	7
6
7	2
8	4
9	6
10					Answer	4	4
Sheet1

 

Upvote 0

R

RJB

Board Regular

Joined

Jul 22, 2002

Messages

149

• Nov 20, 2002

• #19

Heh, pretty sharp, more than one way to skin a cat

Ok, I get the combination of the three ranges into one named range, It just wasn't settling into this old brain before.

Will let you know the results.

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Nov 20, 2002

• #20

On 2002-11-20 01:13, Juan Pablo G. wrote:
Ok, are you ready for this ? you CAN use COUNTIF with multiple ranges, non contigous, etc.[...]

Click to expand...

Just great.

 

Upvote 0