SUMPRODUCT that is going wrong for some reason

[cohlar]

Hello,

I have built a spreadsheet to handle my finances (formulas only, no VBA), and now I am stuck with a formula that I thought would work... but just doesn't. Any help to fix my formula would be greatly appreciated

In my spreadsheet I have:
- the 'Expenses' table with "Account from" and "Account to" dates for each row
- my 'Summary' worksheet in which I have a "StartDate" and "EndDate" which define the period for which I want to know my expenses (for exemple if I want to know what were my expenses in August 2018, I will input "StartDate=01/08/2018" and "EndDate=31/08/2018").

In my 'Summary' worksheet, I want to sum certain types of expenses "within" the defined period - by "within" I mean that if only a portion of the expense Account dates fits into the defined period, then I will want to sum only that portion (linearly).

So I came up with the below formula, and for some reason I get a #VALUE! error

Please Login or Register  to view this content.

Here are the things that I have alreay tried / done:
- I have made the calculation of the "portion" of the expense within the defined period (second line of my formula) within the Expenses table itself, and used a SUMIF in my 'Summary' sheet on the already calculated values... and it works well (so I believe I am calculating the right numbers, it is only an issue of Excel syntax). But this is just a quick fix, it's not good enough because I want to have several summarizing sheets with different dates in each sheet (for example one sheet will show the expenses per month), and I do not like the idea of bringing all the different dates into my Expenses table
- I have tried to replace all the MIN/MAX formulas with simple calculation using MIN(A,B)=(A+B-|A-B|)/2 and MAX(A,B)=(A+B+|A-B|)/2, but it didn't help
- I assumed the problem was that I was mixing arrays with numbers in the MIN / MAX formulas, so I tried to generate arrays with the StartDate / EndDate value repeated... didn't help neither

Any idea of what's wrong with my formula and how to fix it?
Many thanks for your help!

Cheers,
Larry

[Bo_Ry]

Array in Sumproduct need to be same dimension, same # of rows and columns.
Please check # of rows/columns of Expenses[Amount]*(Expenses[Expense Type]=$B25) and (Expenses[Account To]-Expenses[Account From]+1)

[FDibbins]

Hi, welcome to the forum

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

It might help to add a helper column to calc the partial amounts?

[jason.b75]

@Bo_Ry, the data is all in the same table so number of rows would not be an issue.

Looking at the formula, if the Amount, Account From, or Account To columns contain any text, then that will be the root of the problem. This includes any formula that uses "" to show a blank when there is no result.

Other than that, there is no reason for the formula to return that error. It should give a result, even if it is not the one you expect.

[Bo_Ry]

@Bo_Ry, the data is all in the same table so number of rows would not be an issue.

Thanks Jason, You are right. I rarely use table so I tough that was range name.

[cohlar]

Array in Sumproduct need to be same dimension, same # of rows and columns.
Please check # of rows/columns of Expenses[Amount]*(Expenses[Expense Type]=$B25) and (Expenses[Account To]-Expenses[Account From]+1)

Bo_Ry - Indeed, 'Expenses' is the name of a table in my spreadsheet. Thank you jason.b75 for pointing that out. Maybe the "+1" is an issue? (1 is obviously not an array).

[cohlar]

Hi, welcome to the forum

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

It might help to add a helper column to calc the partial amounts?

The file is attached in my response further below.

[cohlar]

Bo_Ry, the data is all in the same table so number of rows would not be an issue.

Looking at the formula, if the Amount, Account From, or Account To columns contain any text, then that will be the root of the problem. This includes any formula that uses "" to show a blank when there is no result.

Other than that, there is no reason for the formula to return that error. It should give a result, even if it is not the one you expect.

Thank you, you were right, I was writing "N/A" in some of the "Account from" and "Account to" dates: I took those out and I now no longer have the error message... I am still not getting the result I am looking for but I think this is a good start. I am now getting zeros: I have tried to remove my "MAX(...,0)" and got large negative figures, so my guess is that my MIN/MAX functions are treating all my arguments as if it was one concatenated arrow.

[cohlar]

Now I can attach my "light" file - pleast find it attached.

I did two things:
1 - I made the calculation of the expense for the "sales period" (and monthly) in the Expenses table: it works, but I don't like this solution because I don't want my Expenses table to depend upon the "sales period"... that would mean that every time I want to make another dashboard with other dates, I'd have to add a column with the corresponding period in my Expenses table
2 - I made two columns in my 'Summary' sheet so you can see the result I am looking for (currently "Calculated in Expenses table") and the result I am currently getting with my current formula ("Calculated in Expenses table")

I hope this will help you help me

Cheers,
Larry

[jason.b75]

Try this array formula in D3 of the summary sheet.

If you get 3195 for transportation and 307 for eu vat compliance then that means that the array is not confirmed correctly (see notes below formula).

=SUMPRODUCT((Expenses[Amount Excl. VAT (EUR)]*(Expenses[Expense Type]=$B3)/(Expenses[Account To]-Expenses[Account From]+1))*(IF(Expenses[Account To]> EndDate,EndDate,Expenses[Account To])-IF(Expenses[Account From]< StartDate,StartDate,Expenses[Account From])+1)*(Expenses[Account From]< EndDate)*(Expenses[Account To]>StartDate))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[cohlar]

SMART, thank you so much for your help!
That's super helpful, my data model is going to be so much cleaner now!

Cheers,
Larry