Count Same Number in three ranges

RJB

Board Regular
Joined
Jul 22, 2002
Messages
149
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)
 
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

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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
I did! You need a new name range that covers the three other ones, then just use the single named range...
 
Upvote 0
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
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)
 
Upvote 0
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
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
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
ABCDEFG
1
21232
3
45678910
5234567
6
72
84
96
10Answer44
Sheet1
 
Upvote 0
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

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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