Count Same Number in three ranges

P

PaddyD

MrExcel MVP

Joined

May 1, 2002

Messages

14,234

• Nov 19, 2002

• #2

anything wrong with 3 countif's, or an equivalent ?!? Excel's not usually too happy performing calculations on non-contiguous ranges, so if the ranges are non-contiguous, having a single range name for the 3 ranges wont solve the problem...

paddy

 

Upvote 0

R

RJB

Board Regular

Joined

Jul 22, 2002

Messages

149

• Nov 19, 2002

• #3

Yes they are contigious. Would the three countifs yield three different answers, like below?
Range1=1 Range2=0 Range3=2
This would solve the problem somewhat, but was hoping to save the space, reduce the possibility of error by getting just get one answer.

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Nov 19, 2002

• #4

On 2002-11-19 17:41, RJB wrote:
Yes they are contigious. Would the three countifs yield three different answers, like below?
Range1=1 Range2=0 Range3=2
This would solve the problem somewhat, but was hoping to save the space, reduce the possibility of error by getting just get one answer.

Click to expand...

What are the exact ranges?

 

Upvote 0

P

PaddyD

MrExcel MVP

Joined

May 1, 2002

Messages

14,234

• Nov 19, 2002

• #5

if the ranges are contiguous, you could simply add a new name that covered all three ranges...

 

Upvote 0

R

RJB

Board Regular

Joined

Jul 22, 2002

Messages

149

• Nov 19, 2002

• #6

Answered the contiguous question wrong. They are NOT contiguous. The formula (if it worked would look something like this)

=Countif(F7:24,F29:F42,F53:F65,"=S2")

S2 is the cell reference from which to compare. That number is derived from a MIN formula over the same ranges.

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Nov 19, 2002

• #7

On 2002-11-19 18:22, RJB wrote:
Answered the contiguous question wrong. They are NOT contiguous. The formula (if it worked would look something like this)

=Countif(F7:24,F29:F42,F53:F65,"=S2")

S2 is the cell reference from which to compare. That number is derived from a MIN formula over the same ranges.

Click to expand...

You can get away with...

=COUNTIF(F7:F24,S2)+COUNTIF(F29:F42,S2)+COUNTIF(F53:F65,S2)

 

Upvote 0

P

PaddyD

MrExcel MVP

Joined

May 1, 2002

Messages

14,234

• Nov 19, 2002

• #8

=Countif(F7:24,F29:F42,F53:F65,"=S2")

Click to expand...

I prefer using sumproduct to sumif / countif ('cos I'm happy with possibly having to sacrifice a little performance for not having to mess about with the quotation marks!)

=sumproduct((F7:F24=s2)+0)+sumproduct((F29:F42=s2)+0)+sumproduct((F53:F65=s2)+0)

 

Upvote 0

R

RJB

Board Regular

Joined

Jul 22, 2002

Messages

149

• Nov 19, 2002

• #9

You win the prize

That worked on existing scenario...I will try it on various scenarios over the next week or so just to make sure.

Thanks very much

 

Upvote 0

R

RJB

Board Regular

Joined

Jul 22, 2002

Messages

149

• Nov 19, 2002

• #10

PaddyD

Now that you are hot, can you do the same thing with the RANK function. Rank results over non-contiguous columns/cells?

References are the same as the SUMPRODUCT formula
=RANK(F7,F7:F24,F32:F45 etc.etc)

 

Upvote 0