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]

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

[RJB]

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.

[Aladin Akyurek]

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.

What are the exact ranges?

[PaddyD]

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

[RJB]

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.

[Aladin Akyurek]

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.

You can get away with...

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

[PaddyD]

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

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)

[RJB]

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

[RJB]

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)