Solved

Still having problems with Sumproduct

Posted on 2011-02-18
12
231 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

912 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

25 Experts available now in Live!

Get 1:1 Help Now