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

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.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...

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

"Stan Brown" wrote:
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.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...


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(--(A1:A9="route2"),--(C1:C9="2"),B1:B9) result 0
=SUMPRODUCT(--(A1:A9="route2"),--(C1:C9="1"),B1:B9) result 1

example:
A B C
1 route item rate
2 route1 subscriber1 1 $1
3 route1 subscriber2 1 $2
4 route1 s.total 2 na
5
6 route item rate
7 route2 subscriber1 1 $1
8 route2 subscriber2 2 $2
9 route2 s.total 2 na

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

On Feb 28, 1:46*pm, Monte wrote:
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



"Stan Brown" wrote:
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.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
* * * * * * * * * * * * * * * * * *http://OakRoadSystems.com
Shikata ga nai...

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(--(A1:A9="route2"),--(C1:C9="2"),B1:B9) * *result 0
=SUMPRODUCT(--(A1:A9="route2"),--(C1:C9="1"),B1:B9) * *result 1

example:
* * *A * * * * * * * * * * * * * B * * * * C
1 route * * * * * * * * * * *item * * rate
2 route1 subscriber1 * * 1 * * * *$1
3 route1 subscriber2 * * 1 * * * *$2
4 route1 s.total * * * * * * 2 * * * *na
5
6 route * * * * * * * * * * *item * * rate
7 route2 subscriber1 * * 1 * * * *$1
8 route2 subscriber2 * * 2 * * * *$2
9 route2 s.total * * * * * * 2 * * * *na- Hide quoted text -

- Show quoted text -

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

"Pete_UK" wrote:
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

On Feb 28, 1:46 pm, Monte wrote:
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



"Stan Brown" wrote:
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.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...

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(--(A1:A9="route2"),--(C1:C9="2"),B1:B9) result 0
=SUMPRODUCT(--(A1:A9="route2"),--(C1:C9="1"),B1:B9) result 1

example:
A B C
1 route item rate
2 route1 subscriber1 1 $1
3 route1 subscriber2 1 $2
4 route1 s.total 2 na
5
6 route item rate
7 route2 subscriber1 1 $1
8 route2 subscriber2 2 $2
9 route2 s.total 2 na