Solved

Still having problems with Sumproduct

Posted on 2011-02-18
12
230 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
  • 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
 
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now