Count/Sum based on mutiple criteria
 

I could not find help on this one. I have the spreadsheet below that I want
to do this: Count the number of times that an SC1 has Rev between 250 and
550. Then I would also like to sum up (in a different column) the rev for the
same criteria. This should be easy with sumproduct but I cannot get it done.
Any help?

Name Division Rev
Tom SC1 0
fish SC3 0
roger SC2 300
steve SC2 900
cindy SC1 650
kim SC1 1100
ryan SC3 210
bill SC1 1200
tony SC1 400
ted SC2 200

Count/Sum based on mutiple criteria
 

To count:

=SUMPRODUCT((B2:B11="SC1")*(C2:C11=250)*(C2:C11<= 550))

To total:

=SUMPRODUCT((B2:B11="SC1")*(C2:C11=250)*(C2:C11<= 550)*C2:C11)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Jon Dow" wrote in message
...
I could not find help on this one. I have the spreadsheet below that I want
to do this: Count the number of times that an SC1 has Rev between 250 and
550. Then I would also like to sum up (in a different column) the rev for
the
same criteria. This should be easy with sumproduct but I cannot get it done.
Any help?

Name Division Rev
Tom SC1 0
fish SC3 0
roger SC2 300
steve SC2 900
cindy SC1 650
kim SC1 1100
ryan SC3 210
bill SC1 1200
tony SC1 400
ted SC2 200

Count/Sum based on mutiple criteria
 

Try this:

=SUMPRODUCT((B2:B10="SC1")*(C2:C10=250)*(C2:C10<= 550))

for the count, and this one for the sum:

=SUMPRODUCT((B2:B10="SC1")*(C2:C10=250)*(C2:C10<= 550)*(C2:C10))

Change the ranges to suit.

Hope this helps.

Pete

On Sep 16, 4:31*pm, Jon Dow wrote:
I could not find help on this one. I have the spreadsheet below that I want
to do this: Count the number of times that an SC1 has Rev between 250 and
550. Then I would also like to sum up (in a different column) the rev for the
same criteria. This should be easy with sumproduct but I cannot get it done.
Any help?

Name * *Division * * * *Rev
Tom * * SC1 * * 0
fish * *SC3 * * 0
roger * SC2 * * 300
steve * SC2 * * 900
cindy * SC1 * * 650
kim * * SC1 * * 1100
ryan * *SC3 * * 210
bill * *SC1 * * 1200
tony * *SC1 * * 400
ted * * SC2 * * 200

Count/Sum based on mutiple criteria
 

This board rocks!! It never lets me down. Thanks for your help

"Jon Dow" wrote:

I could not find help on this one. I have the spreadsheet below that I want
to do this: Count the number of times that an SC1 has Rev between 250 and
550. Then I would also like to sum up (in a different column) the rev for the
same criteria. This should be easy with sumproduct but I cannot get it done.
Any help?

Name Division Rev
Tom SC1 0
fish SC3 0
roger SC2 300
steve SC2 900
cindy SC1 650
kim SC1 1100
ryan SC3 210
bill SC1 1200
tony SC1 400
ted SC2 200

Count/Sum based on mutiple criteria
 

We appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jon Dow" wrote in message
...
This board rocks!! It never lets me down. Thanks for your help

"Jon Dow" wrote:

I could not find help on this one. I have the spreadsheet below that I
want
to do this: Count the number of times that an SC1 has Rev between 250 and
550. Then I would also like to sum up (in a different column) the rev for
the
same criteria. This should be easy with sumproduct but I cannot get it
done.
Any help?

Name Division Rev
Tom SC1 0
fish SC3 0
roger SC2 300
steve SC2 900
cindy SC1 650
kim SC1 1100
ryan SC3 210
bill SC1 1200
tony SC1 400
ted SC2 200