Hellp --- COUNTIF/SUMPRODUCT?? Large DataRange - Formula burps...
 

I have a large range Feb 2-27'!G7:N61 on worksheet 1 that includes monitoring
scores... Then I have a 2nd worksheet that I choose from a dropdown the
Super's Name CELL "E2"... On the 2nd Worksheet I have other cell's formulas
based on Cell "E2" selection that gives me the "Total Agents", "Total
Evaluations", and "Overall Score Avg" for the Supervisor in "E2" Cell... But
I can't seem to get the total Number of Scores over "=90" , and then another
cell that will include the total number of Scores that are "<90"....
here's what I attempted for the 2 formulas =SUMPRODUCT(('Feb
2-27'!$A$7:$A$61=E2)*('Feb 2-27'!$G$7:$N$61=90) and =SUMPRODUCT(('Feb
2-27'!$A$7:$A$61=E2)*('Feb 2-27'!$G$7:$N$61<90)
Any assistance would be greatly appreciated... Thanks

Hellp --- COUNTIF/SUMPRODUCT?? Large DataRange - Formula burps...
 

Hi,

try

SUMPRODUCT(('Feb
2-27'!$A$7:$A$61=E2)*('Feb 2-27'!$G$7:$N$61=90)*G$7:$N$61)
and
=SUMPRODUCT(('Feb
2-27'!$A$7:$A$61=E2)*('Feb 2-27'!$G$7:$N$61<90)*G$7:$N$61)


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"TxBlueEyes" wrote:

I have a large range Feb 2-27'!G7:N61 on worksheet 1 that includes monitoring
scores... Then I have a 2nd worksheet that I choose from a dropdown the
Super's Name CELL "E2"... On the 2nd Worksheet I have other cell's formulas
based on Cell "E2" selection that gives me the "Total Agents", "Total
Evaluations", and "Overall Score Avg" for the Supervisor in "E2" Cell... But
I can't seem to get the total Number of Scores over "=90" , and then another
cell that will include the total number of Scores that are "<90"....
here's what I attempted for the 2 formulas =SUMPRODUCT(('Feb
2-27'!$A$7:$A$61=E2)*('Feb 2-27'!$G$7:$N$61=90) and =SUMPRODUCT(('Feb
2-27'!$A$7:$A$61=E2)*('Feb 2-27'!$G$7:$N$61<90)
Any assistance would be greatly appreciated... Thanks

Hellp --- COUNTIF/SUMPRODUCT?? Large DataRange - Formula burps...
 

Your problem is your ranges must be the same size. A7:a61 is 55 cells, but
g7:n61 is many more. Do you mean g7:g61?

Regards,
Fred.

"TxBlueEyes" wrote in message
...
I have a large range Feb 2-27'!G7:N61 on worksheet 1 that includes
monitoring
scores... Then I have a 2nd worksheet that I choose from a dropdown the
Super's Name CELL "E2"... On the 2nd Worksheet I have other cell's
formulas
based on Cell "E2" selection that gives me the "Total Agents", "Total
Evaluations", and "Overall Score Avg" for the Supervisor in "E2" Cell...
But
I can't seem to get the total Number of Scores over "=90" , and then
another
cell that will include the total number of Scores that are "<90"....
here's what I attempted for the 2 formulas =SUMPRODUCT(('Feb
2-27'!$A$7:$A$61=E2)*('Feb 2-27'!$G$7:$N$61=90) and =SUMPRODUCT(('Feb
2-27'!$A$7:$A$61=E2)*('Feb 2-27'!$G$7:$N$61<90)
Any assistance would be greatly appreciated... Thanks

Hellp --- COUNTIF/SUMPRODUCT?? Large DataRange - Formula burps...
 

Disregard the last answer, I thought you were trying to sum, not count

Instead just add a second ) at the end of the formulas.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"TxBlueEyes" wrote:

I have a large range Feb 2-27'!G7:N61 on worksheet 1 that includes monitoring
scores... Then I have a 2nd worksheet that I choose from a dropdown the
Super's Name CELL "E2"... On the 2nd Worksheet I have other cell's formulas
based on Cell "E2" selection that gives me the "Total Agents", "Total
Evaluations", and "Overall Score Avg" for the Supervisor in "E2" Cell... But
I can't seem to get the total Number of Scores over "=90" , and then another
cell that will include the total number of Scores that are "<90"....
here's what I attempted for the 2 formulas =SUMPRODUCT(('Feb
2-27'!$A$7:$A$61=E2)*('Feb 2-27'!$G$7:$N$61=90) and =SUMPRODUCT(('Feb
2-27'!$A$7:$A$61=E2)*('Feb 2-27'!$G$7:$N$61<90)
Any assistance would be greatly appreciated... Thanks

Hellp --- COUNTIF/SUMPRODUCT?? Large DataRange - Formula burps...
 

not so

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Fred Smith" wrote in message
...
Your problem is your ranges must be the same size. A7:a61 is 55 cells, but
g7:n61 is many more. Do you mean g7:g61?

Regards,
Fred.

"TxBlueEyes" wrote in message
...
I have a large range Feb 2-27'!G7:N61 on worksheet 1 that includes
monitoring
scores... Then I have a 2nd worksheet that I choose from a dropdown the
Super's Name CELL "E2"... On the 2nd Worksheet I have other cell's
formulas
based on Cell "E2" selection that gives me the "Total Agents", "Total
Evaluations", and "Overall Score Avg" for the Supervisor in "E2" Cell...
But
I can't seem to get the total Number of Scores over "=90" , and then
another
cell that will include the total number of Scores that are "<90"....
here's what I attempted for the 2 formulas =SUMPRODUCT(('Feb
2-27'!$A$7:$A$61=E2)*('Feb 2-27'!$G$7:$N$61=90) and =SUMPRODUCT(('Feb
2-27'!$A$7:$A$61=E2)*('Feb 2-27'!$G$7:$N$61<90)
Any assistance would be greatly appreciated... Thanks