Does anyone have a formula that counts the number of occurances in a range of
cells in another sheet based on the following criteria, 1. it equals the
value in a cell on another sheet, and 2. its less than "some value". For
instance I have a list on Sheet 1 that contains 26 names, on another sheet I
have a table of data where column C contains the same names on Sheet 1 only
multiple times, in Column F I have a list of numbers, but I only want to
count those numbers that correspond to the name on Sheet 1 and are over a
certain value, I have tried Count, Count if, Count with and If and I keep
getting formula errors. please help.
--
Thanks

A1 = Name to check
B1 = "some value" to limit the summed numbers

Sheet2-ColumnC = Names
Sheet2=ColumnF = Numbers to sum

=SUMPRODUCT((Sheet2!$C$1:$C$10=A1)
*(Sheet2!$F$1:$F$10B1)
*(Sheet2!$F$1:$F$10))

Adjust the ranges as needed.

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Jerry" wrote:

Does anyone have a formula that counts the number of occurances in a range of
cells in another sheet based on the following criteria, 1. it equals the
value in a cell on another sheet, and 2. its less than "some value". For
instance I have a list on Sheet 1 that contains 26 names, on another sheet I
have a table of data where column C contains the same names on Sheet 1 only
multiple times, in Column F I have a list of numbers, but I only want to
count those numbers that correspond to the name on Sheet 1 and are over a
certain value, I have tried Count, Count if, Count with and If and I keep
getting formula errors. please help.
--
Thanks

Try something like this:

=SUMPRODUCT(--(Sheet2!C$1:C$100=A1),--(Sheet2!F$1:F$100100))


--
Biff
Microsoft Excel MVP


"Jerry" wrote in message
...
Does anyone have a formula that counts the number of occurances in a range
of
cells in another sheet based on the following criteria, 1. it equals the
value in a cell on another sheet, and 2. its less than "some value". For
instance I have a list on Sheet 1 that contains 26 names, on another sheet
I
have a table of data where column C contains the same names on Sheet 1
only
multiple times, in Column F I have a list of numbers, but I only want to
count those numbers that correspond to the name on Sheet 1 and are over a
certain value, I have tried Count, Count if, Count with and If and I keep
getting formula errors. please help.
--
Thanks

Thank you for the reply. I tried it and got the same error as everything
else I tried, the problem seems to be that the first column in sheet two that
I am trying to reference to the cell in column A on my Sheet one is a
"constant", and I cant get around the error. So forgive my first question I
obviously omited some key pieces of information.

My Data looks like this

Sheet 1
Column A
AA
AB
AC
AD
AE
AF

Sheet Two

Column A Column XX

Constant Number
AA 10
AA 300
AB 450
AF 500
AD 30
AD 450
AF 15
AA 200

The Formula you provide failes as its trying to multiply a constant. I need
a formula that looks at cell in a list (Sheet 1, Column A) and goes to the
table on sheet two finds all the times that cell value occures in the table,
looks to the column to the right that I want to count, and counts only those
values that have a number greater than say 365 for the person whos name is
AA...AB...etc.... Sorry if I am rambling.
--
Thanks


"JBeaucaire" wrote:

A1 = Name to check
B1 = "some value" to limit the summed numbers

Sheet2-ColumnC = Names
Sheet2=ColumnF = Numbers to sum

=SUMPRODUCT((Sheet2!$C$1:$C$10=A1)
*(Sheet2!$F$1:$F$10B1)
*(Sheet2!$F$1:$F$10))

Adjust the ranges as needed.

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Jerry" wrote:

Does anyone have a formula that counts the number of occurances in a range of
cells in another sheet based on the following criteria, 1. it equals the
value in a cell on another sheet, and 2. its less than "some value". For
instance I have a list on Sheet 1 that contains 26 names, on another sheet I
have a table of data where column C contains the same names on Sheet 1 only
multiple times, in Column F I have a list of numbers, but I only want to
count those numbers that correspond to the name on Sheet 1 and are over a
certain value, I have tried Count, Count if, Count with and If and I keep
getting formula errors. please help.
--
Thanks

With your sample data, this works for me on Sheet1:

=SUMPRODUCT((Sheet2!$A$1:$A$8=Sheet1!A1)*(Sheet2!$ B$1:$B$8365)*(Sheet2!$B$1:$B$8))

NOTE: the first answer IS zero, so you don't see any results until you copy
it down.
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Jerry" wrote:

Thank you for the reply. I tried it and got the same error as everything
else I tried, the problem seems to be that the first column in sheet two that
I am trying to reference to the cell in column A on my Sheet one is a
"constant", and I cant get around the error. So forgive my first question I
obviously omited some key pieces of information.

My Data looks like this

Sheet 1
Column A
AA
AB
AC
AD
AE
AF

Sheet Two

Column A Column XX

Constant Number
AA 10
AA 300
AB 450
AF 500
AD 30
AD 450
AF 15
AA 200

The Formula you provide failes as its trying to multiply a constant. I need
a formula that looks at cell in a list (Sheet 1, Column A) and goes to the
table on sheet two finds all the times that cell value occures in the table,
looks to the column to the right that I want to count, and counts only those
values that have a number greater than say 365 for the person whos name is
AA...AB...etc.... Sorry if I am rambling.
--
Thanks


"JBeaucaire" wrote:

A1 = Name to check
B1 = "some value" to limit the summed numbers

Sheet2-ColumnC = Names
Sheet2=ColumnF = Numbers to sum

=SUMPRODUCT((Sheet2!$C$1:$C$10=A1)
*(Sheet2!$F$1:$F$10B1)
*(Sheet2!$F$1:$F$10))

Adjust the ranges as needed.

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Jerry" wrote:

Does anyone have a formula that counts the number of occurances in a range of
cells in another sheet based on the following criteria, 1. it equals the
value in a cell on another sheet, and 2. its less than "some value". For
instance I have a list on Sheet 1 that contains 26 names, on another sheet I
have a table of data where column C contains the same names on Sheet 1 only
multiple times, in Column F I have a list of numbers, but I only want to
count those numbers that correspond to the name on Sheet 1 and are over a
certain value, I have tried Count, Count if, Count with and If and I keep
getting formula errors. please help.
--
Thanks

Again thank you, but here is my result.

=SUMPRODUCT(('Report 01-05'!E:E=Sheet1!A3)*('Report 01-05'!W:W365)*('Report
01-05'!W:W)) cant get past the #NUM Error, A3 as a constant. If I make the
ranges fixed then the program chugs and instead of returning the total number
above 365 I get the product of each item over 365. I just need to count "how
many ocurances" are over 365. and I need the ranges variable so the guy who
runs the report each month doesnt have to change any of the formulas.
--
Thanks


"JBeaucaire" wrote:

With your sample data, this works for me on Sheet1:

=SUMPRODUCT((Sheet2!$A$1:$A$8=Sheet1!A1)*(Sheet2!$ B$1:$B$8365)*(Sheet2!$B$1:$B$8))

NOTE: the first answer IS zero, so you don't see any results until you copy
it down.
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Jerry" wrote:

Thank you for the reply. I tried it and got the same error as everything
else I tried, the problem seems to be that the first column in sheet two that
I am trying to reference to the cell in column A on my Sheet one is a
"constant", and I cant get around the error. So forgive my first question I
obviously omited some key pieces of information.

My Data looks like this

Sheet 1
Column A
AA
AB
AC
AD
AE
AF

Sheet Two

Column A Column XX

Constant Number
AA 10
AA 300
AB 450
AF 500
AD 30
AD 450
AF 15
AA 200

The Formula you provide failes as its trying to multiply a constant. I need
a formula that looks at cell in a list (Sheet 1, Column A) and goes to the
table on sheet two finds all the times that cell value occures in the table,
looks to the column to the right that I want to count, and counts only those
values that have a number greater than say 365 for the person whos name is
AA...AB...etc.... Sorry if I am rambling.
--
Thanks


"JBeaucaire" wrote:

A1 = Name to check
B1 = "some value" to limit the summed numbers

Sheet2-ColumnC = Names
Sheet2=ColumnF = Numbers to sum

=SUMPRODUCT((Sheet2!$C$1:$C$10=A1)
*(Sheet2!$F$1:$F$10B1)
*(Sheet2!$F$1:$F$10))

Adjust the ranges as needed.

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Jerry" wrote:

Does anyone have a formula that counts the number of occurances in a range of
cells in another sheet based on the following criteria, 1. it equals the
value in a cell on another sheet, and 2. its less than "some value". For
instance I have a list on Sheet 1 that contains 26 names, on another sheet I
have a table of data where column C contains the same names on Sheet 1 only
multiple times, in Column F I have a list of numbers, but I only want to
count those numbers that correspond to the name on Sheet 1 and are over a
certain value, I have tried Count, Count if, Count with and If and I keep
getting formula errors. please help.
--
Thanks

T. Valko already suggested a formula to give you a count. I was continuing
because I thought it was the sum you wanted.

His answer works, and you really shouldn't use the whole column.

=SUMPRODUCT(--(Sheet2!$A$1:$A$100=A1),
--(Sheet2!$B$1:$B$100365))

That formula is not an array, so ENTER works just fine.

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Jerry" wrote:

Again thank you, but here is my result.

=SUMPRODUCT(('Report 01-05'!E:E=Sheet1!A3)*('Report 01-05'!W:W365)*('Report
01-05'!W:W)) cant get past the #NUM Error, A3 as a constant. If I make the
ranges fixed then the program chugs and instead of returning the total number
above 365 I get the product of each item over 365. I just need to count "how
many ocurances" are over 365. and I need the ranges variable so the guy who
runs the report each month doesnt have to change any of the formulas.
-- Thanks

Havnt tried that yet, went down your path and forgot about it. I am not
familiar with the formula but will give it try--- and thank you again
--
Thanks


"JBeaucaire" wrote:

T. Valko already suggested a formula to give you a count. I was continuing
because I thought it was the sum you wanted.

His answer works, and you really shouldn't use the whole column.

=SUMPRODUCT(--(Sheet2!$A$1:$A$100=A1),
--(Sheet2!$B$1:$B$100365))

That formula is not an array, so ENTER works just fine.

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Jerry" wrote:

Again thank you, but here is my result.

=SUMPRODUCT(('Report 01-05'!E:E=Sheet1!A3)*('Report 01-05'!W:W365)*('Report
01-05'!W:W)) cant get past the #NUM Error, A3 as a constant. If I make the
ranges fixed then the program chugs and instead of returning the total number
above 365 I get the product of each item over 365. I just need to count "how
many ocurances" are over 365. and I need the ranges variable so the guy who
runs the report each month doesnt have to change any of the formulas.
-- Thanks