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)

 

[Aladin Akyurek]

On 2002-11-20 02:16, RJB wrote:
Heh, pretty sharp, more than one way to skin a cat

You mean what Juan suggests... Well, that should be the way you should skin your cat. Forget the other ways.

 

[PaddyD]

JPG,

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


Excellent

paddy

 

[Juan Pablo González]

On 2002-11-20 15:53, PaddyD wrote:
JPG,

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


Excellent

paddy

Also:

Array entered

=SUM(COUNTIF(INDIRECT("Rng"&ROW($A$1:$A$3)),2))

you can also use this to sum different sheets.

 

[kwg]

Thank you all for the help on countif. Will be testing this week. :P

 

[Medusa501]

Counting

This might be 3 years old, but it saved me. Thank you.

 

[Aladin Akyurek]

Re: Counting

This might be 3 years old, but it saved me. Thank you.

Also something to consider: ARRAY.JOIN of the morefunc.xll add-in.

 

[dakota]

would the sumproduct also work to count items on three different sheets in the same workbook?

 

[Aladin Akyurek]

would the sumproduct also work to count items on three different sheets in the same workbook?

Care to elaborate?

 

[dakota]

I have 5 sheets that I need to count the number of time a names is listed.
I have tried the following
=SUM(COUNTIF(INDIRECT({"Over8","Over14","Over21"}),C57))
Over8, Over14 and Over21 are named ranges
C57 is the name
the above calc work if all names = C57
I also have two other sheets with names ranged Missingdata and DataInconsitent and I need to look for names on all 5 sheets
Issue format of the name is different between reports.
1 report will have a space before name, the next report will have a space after name and others will have no spaces.
Is there a formula I can use that will look for name that is "like" or "contains"

Example
Sheet 1 has "Sam Spade" no spaces
Sheet 2 has " Sam Spade" space before
Sheet 3 has "Sam Spade " space after

 

[Aladin Akyurek]

I have 5 sheets that I need to count the number of time a names is listed.
I have tried the following
=SUM(COUNTIF(INDIRECT({"Over8","Over14","Over21"}),C57))
Over8, Over14 and Over21 are named ranges
C57 is the name
the above calc work if all names = C57
I also have two other sheets with names ranged Missingdata and DataInconsitent and I need to look for names on all 5 sheets
Issue format of the name is different between reports.
1 report will have a space before name, the next report will have a space after name and others will have no spaces.
Is there a formula I can use that will look for name that is "like" or "contains"

Example
Sheet 1 has "Sam Spade" no spaces
Sheet 2 has " Sam Spade" space before
Sheet 3 has "Sam Spade " space after

Does the following...

=SUMPRODUCT(COUNTIF(INDIRECT({"Over8","Over14","Over21"}),"*"&C57&"*"))

which requires just enter, meets your needs?