Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help With SUMPRODUCT
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help With SUMPRODUCT
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" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help With SUMPRODUCT
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help With SUMPRODUCT
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. ]-[ |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help With SUMPRODUCT
Ken, Do you really have data in all rows? It may be quicker if you limit the scope of the formula -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=503823 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help With SUMPRODUCT
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help With SUMPRODUCT
E$1 is the heading, which should say 'Own Use'
-- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Ken Zenachon" wrote in message ups.com... 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help With SUMPRODUCT
Sorry, miscommunication on my part--
The column heading was actually "Sold To", not "Own Use". My fault. Thanks for you help, Bob! ]-[ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct | Excel Worksheet Functions | |||
sumproduct causing memory errors? | Excel Worksheet Functions | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |