Need Help With SUMPRODUCT

[Ken Zenachon]

I'm still learning to flex my SUMPRODUCT muscles. Here's my latest
challenge:

Workbook contains 2 worksheets: Sales and SaleTotals.
Relevant columns on Sales: Sold To, Date and Sale Price (corresponding
to letters A, D and E, respecively)
The "Sold To" column contains the names of customers and the text "Own
Use".

On the SaleTotals page I want to divy up the sales by tax period, and
for each period show how much (in dollar value) we sold and how much
we kept for our own use.

Columns on SalesTotal: From, Until, Sales, SalesTax, Own Use, OwnUseTax
(corresponding to letters A-F, respectively)
"From" and "Until" are the taxation period begin and end dates.

Now, I came up with this fomula for cell SaleTotals!E3 to calculate the
"Own Use" amount. It looks like it works but it sure is a heck of a
long string. Can this be made shorter?

=SUMPRODUCT(--(Sales!$A$2:$A$65535="Own
Use"),--(Sales!$D$2:$D$65535>='Sales
Totals'!$A3),--(Sales!$E$2:$E$65535))-SUMPRODUCT(--(Sales!$A$2:$A$65535="Own
Use"),--(Sales!$D$2:$D$65535>='Sales
Totals'!$B3),--(Sales!$E$2:$E$65535))

(Sorry if the formula breaks across multiple lines)

What's the most computationally efficient way to calculate the sums I
need?

KZ

[Bob Phillips]

Try this

=SUMPRODUCT(--(Sales!$A$2:$A$65535=E$1),--(Sales!$D$2:$D$65535>=$A3),--(Sale
s!$D$2:$D$65535<=$B3),--(Sales!$E$2:$E$65535))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Ken Zenachon" <[email protected]> wrote in message
news:[email protected]...
> I'm still learning to flex my SUMPRODUCT muscles. Here's my latest
> challenge:
>
> Workbook contains 2 worksheets: Sales and SaleTotals.
> Relevant columns on Sales: Sold To, Date and Sale Price (corresponding
> to letters A, D and E, respecively)
> The "Sold To" column contains the names of customers and the text "Own
> Use".
>
> On the SaleTotals page I want to divy up the sales by tax period, and
> for each period show how much (in dollar value) we sold and how much
> we kept for our own use.
>
> Columns on SalesTotal: From, Until, Sales, SalesTax, Own Use, OwnUseTax
> (corresponding to letters A-F, respectively)
> "From" and "Until" are the taxation period begin and end dates.
>
> Now, I came up with this fomula for cell SaleTotals!E3 to calculate the
> "Own Use" amount. It looks like it works but it sure is a heck of a
> long string. Can this be made shorter?
>
> =SUMPRODUCT(--(Sales!$A$2:$A$65535="Own
> Use"),--(Sales!$D$2:$D$65535>='Sales
>
Totals'!$A3),--(Sales!$E$2:$E$65535))-SUMPRODUCT(--(Sales!$A$2:$A$65535="Own
> Use"),--(Sales!$D$2:$D$65535>='Sales
> Totals'!$B3),--(Sales!$E$2:$E$65535))
>
> (Sorry if the formula breaks across multiple lines)
>
> What's the most computationally efficient way to calculate the sums I
> need?
>
> KZ
>

[Ken Zenachon]

Bob, I'm not sure what you did in the first argument,
(Sales!$A$2:$A$65535=E$1) What's E1 got to do with anything?

KZ

[Ken Zenachon]

I just changed "E$1" to "Own Use" and it works fine.
Still, put a dozen or so of these formulas on a page and it takes a
couple of seconds for the sheet to update every time I enter a new
record.
I'm running an Athlon XP 2600+ with 1GB of generic RAM. You'd think a
few simple math calculations wouldn't cause a fast CPU to skip a beat,
and yet it does.
Oh well.


]-[

[daddylonglegs]

Ken,

Do you really have data in all rows?

It may be quicker if you limit the scope of the formula

[Ken Zenachon]

Yeah, I just noticed that I defaulted from rows 2 to 65,535. I limited
the scope and the refreshes sped up, although calculations still take
longer than I would think is necessary.

KZ

[Bob Phillips]

E$1 is the heading, which should say 'Own Use'

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Ken Zenachon" <[email protected]> wrote in message
news:[email protected]...
> Bob, I'm not sure what you did in the first argument,
> (Sales!$A$2:$A$65535=E$1) What's E1 got to do with anything?
>
> KZ
>

[Ken Zenachon]

Sorry, miscommunication on my part--
The column heading was actually "Sold To", not "Own Use".
My fault.

Thanks for you help, Bob!


]-[