CODEWITHSUNDEEP
excelexcel-formula
ccampj
ccampj

Reputation: 725

SUM PRODUCT with DATE and COLUMN functions not working - Why not?

I am having a problem with the SUMPRODUCT function. I have found a less than ideal work around and would like to see if someone could explain why my ideal formula doesn't work. Below is simplified view of my spreadsheet:

Excel Spreadsheet

The first table tabCheckout is the list of books I have checked out and the qty (I really like Gone with the wind). (no formulas)

The second table tabBooks is my inventory. (no formulas)

Row 5 is the day of the month 1-31 (no formulas)

Row 6 is where the problem occurs. It should show me the available qty for each day of the month.

Here is the formula that works fine in cell C6:

=INDEX(tabBooks,MATCH($B6,tabBooks[Book],0),2)-SUMPRODUCT((tabCheckout[Checkout]<=DATE(thisYear,thisMonth,C5))*(tabCheckout[Checkin]>=DATE(thisYear,thisMonth,C5+1))*(tabCheckout[Book]=$B6)*(tabCheckout[Qty]))

My goal was to replace the reference to C5 in the formula with:

OFFSET(startDate,0,COLUMN()-COLUMN(startDate))

Where startDate = C5. I tried that in cell E6, and it returns a #VALUE! error (hidden by IFERROR). I took out the whole DATE function and put it in cell E7 and it works fine.

Cell E6 formula:

=INDEX(tabBooks,MATCH($B6,tabBooks[Book],0),2)-SUMPRODUCT((tabCheckout[Checkout]<=DATE(thisYear,thisMonth,OFFSET(startDate,0,COLUMN()-COLUMN(startDate))))*(tabCheckout[Checkin]>=DATE(thisYear,thisMonth,E5+1))*(tabCheckout[Book]=$B6)*(tabCheckout[Qty]))

Cell E7 formula:

=DATE(thisYear,thisMonth,OFFSET(startDate,0,COLUMN()-COLUMN(startDate)))

Instead of using the OFFSET function, I was desperate and tried the INDIRECT function in cell G6:

=IFERROR(INDEX(tabBooks,MATCH($B6,tabBooks[Book],0),2)-SUMPRODUCT((tabCheckout[Checkout]<=DATE(thisYear,thisMonth,INDIRECT("F" & COLUMN()-COLUMN(startDate)+1)))*(tabCheckout[Checkin]>=DATE(thisYear,thisMonth,G5+1))*(tabCheckout[Book]=$B6)*(tabCheckout[Qty])),"")

I got really, really desperate and tried the DATEVALUE function:

=IFERROR(INDEX(tabBooks,MATCH($B6,tabBooks[Book],0),2)-SUMPRODUCT((tabCheckout[Checkout]<=DATEVALUE(thisMonth & "/" & OFFSET(startDate,0, COLUMN()-COLUMN(startDate),1,1) & "/" & thisYear))*(tabCheckout[Checkin]>=DATE(thisYear,thisMonth,J5+1))*(tabCheckout[Book]=$B6)*(tabCheckout[Qty])),"")

It seems that the SUMPRODUCT function in combination with any DATE* function that uses some form of COLUMN() or ROW() doesn't work. In my real spreadsheet, each week starts with a named range that I then want to OFFSET from using the COLUMN function. I have never seen Excel return a valid value in one cell but won't if you merge the formulas. That's how I usually build up complicated formulas.

Any help as to why the combination of SUMPRODUCT > DATE* > COLUMN() doesn't work would be appreciated.

Upvotes: 0

Views: 1130

Answers (1)

Axel Richter
Axel Richter

Reputation: 61985

First of all: I'm not sure whether the complexity of those formulas are really necessary. Thats why my answer is not really the complete answer to this question. But the question contains the question "Why OFFSET works different whether in array context or not?" as a part. The answer to this part of the question is:

SUMPRODUCT takes all of it's arguments in array context. That is, they will be evaluated as if they were within an array formula.

OFFSETand INDIRECT have volatile behavior. That is, all changes of the worksheet, not only changes of the arguments of OFFSETand INDIRECT, will lead to a recalculation of OFFSETand INDIRECT formulas. Within array context this behavior leads to a kind of endless loop, which leads to the #VALUE error, except we stop this by using the results of OFFSETand INDIRECT as arguments for a non volatile function.

In other words: OFFSET returns a cell reference like X123. In normal context this reference will be evaluated as it is and returns the content of this cell. In array context the returned reference will be evaluated like {X123} which results in #VALUE because of the volatile behavior of OFFSET. We have to use the results of OFFSET as arguments for a non volatile function to repair this. In this case the N() function offers because we need numbers from OFFSET.

...SUMPRODUCT((tabCheckout[Checkout]<=DATE(thisYear,thisMonth,N(OFFSET(startDate,0‌​,COLUMN()-COLUMN(startDate)))))*...)

In other cases maybe T() has to be used, because we need text.

Upvotes: 1

Related Questions