sumproduct assistance

[Monte]

I'm currently using the formula below to calculate the number of subscribers,
at each subscription rate, on each delivery route. It works when the
subscriber receives a quantity of one, but not when the subscriber receives
more than one (see route2 s.total). Any assistance is appreciated.

=SUMPRODUCT(--(route range=route name),--(rate range=rate),item range)

example:
route item rate
route1 subscriber1 1 $1
route1 subscriber2 1 $2
route1 s.total 2 na

route item rate
route2 subscriber1 1 $1
route2 subscriber2 2 $2
route2 s.total 1 na

 

[Stan Brown]

Thu, 28 Feb 2008 02:05:00 -0800 from Monte

I'm currently using the formula below to calculate the number of subscribers,
at each subscription rate, on each delivery route. It works when the
subscriber receives a quantity of one, but not when the subscriber receives
more than one (see route2 s.total). Any assistance is appreciated.

=SUMPRODUCT(--(route range=route name),--(rate range=rate),item range)

example:
route item rate
route1 subscriber1 1 $1
route1 subscriber2 1 $2
route1 s.total 2 na

route item rate
route2 subscriber1 1 $1
route2 subscriber2 2 $2
route2 s.total 1 na

I can't be certain, since you didn't show your actual formula. But
where you wrote "--(rate range=rate)", which is a test for rate
equaling some predetermined rate, I think you wanted "rate range",
which is simply the applicable rate.

 

[Monte]

Stan-
Thanks for the attempt. To clarify, I'm using two worksheets. The first
contains data, the second, a report containing the number of subscribers by
route/rank. I updated the formula and example below. The result of the first
query (# of $2 items purchased on route2) equals zero. If I change the
quantity of items to 1, the formula's result is 1.
Konran suru...
-Monte

 

[Pete_UK]

If you just want to count the number of subscribers, then try it like
this:

=SUMPRODUCT(--(A1:A9="route2"),--(C1:C9="2"))

Hope this helps.

Pete

 

[Monte]

Pete-
Thanks for the suggestion. However, I need to know the number of items at
each rate on each route.
Cheers.
-Monte