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

x

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

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

Author 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

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

Commented:
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

Commented:
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 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

Commented:
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 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

Author 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

Author Commented:
You are the man, Barry. Thank you very much.
0

Author 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.