Still having problems with Sumproduct


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
BBluAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
barry houdiniConnect With a Mentor Commented:
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
 
jppintoConnect With a Mentor Commented:
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
 
BBluAuthor Commented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
jppintoConnect With a Mentor Commented:
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
 
jppintoCommented:
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
 
barry houdiniCommented:
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
 
BBluAuthor Commented:
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
 
barry houdiniCommented:
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
 
BBluAuthor Commented:
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
 
BBluAuthor Commented:
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
 
BBluAuthor Commented:
You are the man, Barry. Thank you very much.
0
 
BBluAuthor Commented:
Fantastic help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.