count the score greater than 0 & less than 4

Hi,

I am preparing a quality report for my call center in MS Excel 2003. i want to calculate the score for specific heading for seperate regions. the formula is given below

=IF($M$5="All Region",SUMIF('CSAT Data'!$E$2:$E$50734,$H$5,'CSAT Data'!$J$2:$J$50734),SUMIF('CSAT Data'!$A$2:$A$50734,CONCATENATE($H$5,$M$5),'CSAT Data'!J$2:J$50734))

in 'CSAT Data'!J$2:J$50734 is the call score and i want to count the score greater than 0 & less than 4.........

plz help me i m in middle of something very urgent...........

Hi Guarav_dey,

Untested, but you might try:

=IF($M$5="All Region",SUMPRODUCT(--('CSAT Data'!$E$2:$E$50734=$H$5),--('CSAT Data'!$J$2:$J$50734>0),--('CSAT Data'!$J$2:$J$50734<4),'CSAT Data'!$J$2:$J$50734),SUMPRODUCT(--('CSAT Data'!$A$2:$A$50734=$H$5&$M$5),--('CSAT Data'!$J$2:$J$50734>0),--('CSAT Data'!$J$2:$J$50734<4),'CSAT Data'!$J$2:$J$50734))

Hi,

It's working but i need the count of score and it's showing me the sum....

based on Paul's suggestion and without being able to see any sample data, maybe

=IF($M$5="All Region",SUMPRODUCT(--('CSAT Data'!$E$2:$E$50734=$H$5),--('CSAT Data'!$J$2:$J$50734>0),--('CSAT Data'!$J$2:$J$50734<4)),SUMPRODUCT(--('CSAT Data'!$A$2:$A$50734=$H$5&$M$5),--('CSAT Data'!$J$2:$J$50734>0),--('CSAT Data'!$J$2:$J$50734<4)))

If that's not the solution, please post a small data sample. Please do NOT post a file with 50734 rows of data. A few dozen rows of sample data will suffice.

cheers