Count Same Number in three ranges

[RJB]

Need a formula that will count the number of occurrences of that same number in three different ranges. The number that is to be compared to is in a cell that is a result of a formula. Example:
Lowest number found in three ranges: 30 (Answer is in A1)
Question: how many times does that number appear in the three ranges?
Syntax:
=Countif(Range1,range2,range3=A1)
Has to equal the cell reference value (A1), not a plain number (like 30)

 

[PaddyD]

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

 

[RJB]

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)

 

[PaddyD]

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

 

[RJB]

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.

 

[PaddyD]

I am missing something small.

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)

 

[RJB]

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.

 

[Juan Pablo González]

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.

 

[Juan Pablo González]

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

 

[RJB]

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.

 

[Aladin Akyurek]

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.[...]

Just great.