K
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
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