Count/Sum based on mutiple criteria

[Jon Dow]

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

 

[RagDyeR]

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!
=====================================================


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

 

[Pete_UK]

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

 

[Jon Dow]

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

 

[RagDyer]

We appreciate the feed-back.