SUMPRODUCT and range of values

[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

 

[Andy B]

Kristina

Try this:

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

Andy.

 

[Bob Phillips]

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?

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)

 

[Peo Sjoblom]

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)

 

[Kristina]

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





.