Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
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 |