Solved

Still having problems with Sumproduct

Posted on 2011-02-18
12
234 Views
Last Modified: 2012-05-11

I'm still trying to figure out how to use sumproduct properly.  I'm building a dashboard that highlights several pieces of information.  I have an excel sheet that connects to an access database. One part in particular, I'm pulling down a table/query that lists expense report and vendor checks.  I'm trying to show the balance of the "request bucket" for each day over the last two weeks.  So I have a table with dates, descending from today's date. So 2/18/11, 2/17/11, 2/16/11, etc. In the column next to the dates, I have the following formula:

=SUMPRODUCT((Bills_last6Mo!S:S>VALUE(B4))*(Bills_last6Mo!Z:Z<VALUE(B4))*(Bills_last6Mo!E:E))

Column S is the date we mailed the check pmt. out
Column Z is the date we added it to our "to pay" queue

I'm basically trying to get a picture of the daily balance of these pmt. requests to see at any point in time how much we are holding before we send them out.  So, I'm just trying to get the total for each date of the items that have a mail date that is more than the current date (column to left) but a "add to queue date" that is less than the current date (column to left; descending order of date, starting with current)

I hope I'm explaining this fine.  I'm getting a "#VALUE!" error. TestDashboard.xls
0
Comment
Question by:BBlu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
12 Comments
 
LVL 33

Assisted Solution

by:jppinto
jppinto earned 100 total points
ID: 34929492
The problem is that, with this formula:

=SUMPRODUCT((Data!S:S>VALUE(B4))*(Data!Z:Z<VALUE(B4))*(Data!E:E))

You are getting also the column headers into your formula! You should change it to something like this:

=SUMPRODUCT((Data!S5:S10000>VALUE(B4))*(Data!Z5:Z10000<VALUE(B4))*(Data!E5:E10000))

This will work.

jppinto
0
 

Author Comment

by:BBlu
ID: 34929706
Perfect! Thanks, jppinto.  One more minor question.  If I want, in the first row, only to add the OR condition if the sent date (column S) is blank.  In other words, for the current date, I want to add the checks that have been added to th queue whose dates are after the current date OR (in these cases) null.
0
 
LVL 33

Assisted Solution

by:jppinto
jppinto earned 100 total points
ID: 34929745
To make an OR with Sumproduct, you must sum 2 sumproducts, one with each condition of the "OR", like this:

=SUMPRODUCT((Data!S5:S10000>VALUE(B4))*(Data!Z5:Z10000<VALUE(B4))*(Data!E5:E10000))+SUMPRODUCT((Data!S5:S10000="")*(Data!Z5:Z10000<VALUE(B4))*(Data!E5:E10000))

jppinto
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 33

Expert Comment

by:jppinto
ID: 34929767
Or try like this:

=SUMPRODUCT((Data!S5:S10000={">B4",""})*(Data!Z5:Z10000<VALUE(B4))*(Data!E5:E10000))

PS: both last two formulas not tested!
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34930003
I don't know why you need VALUE function, if you change the syntax slightly this formula will work for your initial query

=SUMPRODUCT((Data!S:S>B4)*(Data!Z:Z<B4),Data!E:E)

although SUMIFS is preferable, i.e.

=SUMIFS(Data!E:E,Data!S:S,">"&B4,Data!Z:Z,"<"&B4)

For the last condition try

=SUMPRODUCT(((Data!S:S>B4)+(Data!S:S="")>0)*(Data!Z:Z<B4),Data!E:E)

regards, barry
0
 

Author Comment

by:BBlu
ID: 34932023
Thanks to you both.  The last thing to fix, it seems like, is the fact that since I'm trying to get, for any given date, any pmt that has been added to the queue, but not mailed yet.  My condition of "added to queue on a date less than the current date" doesn't work for ones that have not been added to the queue at all.  So I need to add one more condition of a queue date that is not null.

So, the final formula should address the following, using today (2/19/11) as the example:
Any pmt. request added today or before today (mailed date<=2/19/11; not null) but not mailed at all or mailed after today (>2/19/11 or null)

I hope that makes sense and I thank you very, very much for your help.  I've been wracking my brain trying to figure it out myself and learn sumproduct along the way.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34932876
OK, I think you can do that with a small tweak to the last formula I suggested, i.e.

=SUMPRODUCT(((Data!S:S>B4)+(Data!S:S="")>0)*(Data!Z:Z<=B4)*(Data!Z:Z<>""),Data!E:E)

The + is effectively giving you "OR", whereas the * is effectively "AND"....

SUMPRODUCT can be inefficient if you use whole columns so it might be best to use a specific range, e.g. 10000 rows like

=SUMPRODUCT(((Data!S1:S10000>B4)+(Data!S1:S10000="")>0)*(Data!Z1:Z10000<=B4)*(Data!Z1:Z10000<>""),Data!E1:E10000)

regards, barry
0
 

Author Comment

by:BBlu
ID: 34933844
Thanks, again, Barry.  It's returning a value of zero.  I'm trying to work through what;s causing the issue.  Can you help me understand the what the ">0" part does in :
((Data!S1:S10000>B4)+(Data!S1:S10000="")>0)
0
 

Author Comment

by:BBlu
ID: 34933878
I take it back, Barry.  The range wasn't big enough. I think it works now.  If you could help me understand what the ">0" means, I think I can go ahead and close this puppy out.  
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 400 total points
ID: 34934231
I included the >0 as a precaution. In your data the blank cells are true blank cells so thia version will give the same result

=SUMPRODUCT(((Data!S1:S10000>B4)+(Data!S1:S10000=""))*(Data!Z1:Z10000<=B4)*(Data!Z1:Z10000<>""),Data!E1:E10000)

.....BUT if the blanks were "null values" returned by a formula then there would be a danger that you would double count some rows because "" returned by a formula is BOTH equal to "" and >B4 (because text values are always deemed to be greater than numeric values). In that case if you add the arrays with + then the result would be 2 for that row and you would double count...using >0 returns TRUE if 2 or 1 is returned so removes the risk of double counting

regards, barry
0
 

Author Comment

by:BBlu
ID: 34934594
You are the man, Barry. Thank you very much.
0
 

Author Closing Comment

by:BBlu
ID: 34934605
Fantastic help
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question