SUMPRODUCT and range of values

  • Thread starter Thread starter Kristina
  • Start date Start date
K

Kristina

I am trying to create a sumproduct formula that will
evaluate several cases, one of which is a range of values.

=SUMPRODUCT((A1:A5="Type1")*(AND(B1:B5>=1,B1:B5<1000)))

What I want to return is the number of times that Type1
corresponds to a value between 1 and 1000.

A related question, does sumproduct work differently in
Excel 2000 vs Excel XP?

Thanks,

Kristina
 
Kristina said:
I am trying to create a sumproduct formula that will
evaluate several cases, one of which is a range of values.

=SUMPRODUCT((A1:A5="Type1")*(AND(B1:B5>=1,B1:B5<1000)))

What I want to return is the number of times that Type1
corresponds to a value between 1 and 1000.

A related question, does sumproduct work differently in
Excel 2000 vs Excel XP?
Click to expand...

No, it's the same AFAIK.

Try

=SUMPRODUCT((A1:A5="Type1")*(B1:B5>=1)*(B1:B5<1000))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
If you are counting these values where the condition is true

=SUMPRODUCT(--(A1:A5="Type1"),--(B1:B5>=1),--(B1:B5<1000))

if you are summing
the values in B1:B5 with the above conditions

=SUMPRODUCT(--(A1:A5="Type1"),--(B1:B5>=1),--(B1:B5<1000),B1:B5)
 
Thanks for all the help - I really appreciate it.
-----Original Message-----
If you are counting these values where the condition is true
(B1:B5<1000))

if you are summing
the values in B1:B5 with the above conditions

=SUMPRODUCT(--(A1:A5="Type1"),--(B1:B5>=1),-- (B1:B5<1000),B1:B5)



--

Regards,

Peo Sjoblom





.
Click to expand...
 
Back
Top