LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.misc
Ken Zenachon
 
Posts: n/a
Default 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

 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct Peter B Excel Worksheet Functions 1 March 7th 05 01:59 PM
sumproduct causing memory errors? dave Excel Worksheet Functions 1 March 3rd 05 09:31 AM
Can I reference =, <, or > sign in SUMPRODUCT BobT Excel Discussion (Misc queries) 7 February 16th 05 01:58 PM
Sumproduct function not working Scott Summerlin Excel Worksheet Functions 12 December 4th 04 05:15 AM
adding two sumproduct formulas together ski2004_2005 Excel Worksheet Functions 1 November 12th 04 09:08 PM


All times are GMT +1. The time now is 11:58 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"