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

[COLOR=#333333]=SUMPRODUCT(Expenses[Amount]*(Expenses[Expense Type]=$B25),
[/COLOR][COLOR=#333333]MAX( MIN(Expenses[Account To],EndDate) - MAX(Expenses[Account From],StartDate) +1 , 0 ) / (Expenses[Account To]-Expenses[Account From]+1))[/COLOR]

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

 

[Oaktree]

SUMPRODUCT requires that each of the multiplying arrays have the same dimensions.

Your MAX and MIN calculations are only returning a single value... so, SUMPRODUCT is trying to take array * array * single value and it doesn't know how to handle that, hence the #VALUE ! error.

What I would do is add a column to your data for "Amount to Include" that does the linear math you're looking for based on the start & end dates.

Then use a simpler

=SUMPRODUCT(Expenses[Amount to Include], --(Expenses[Expense Type]=$B25))

formula.

edit: changed "number of rows" to "dimensions" to be more accurate.

 

[cohlar]

Thank you for your response - you are right, it has to do with me mixing arrays and signe values... but I can't find a solution other than the one you proposed.

I went for your proposed solution for now - and it works well. But I see this as a quick fix, because I have several "Summary" pages with different start dates and end dates on them.
For example I have a summary page with all my monthly exepenses from January to December. So that means that for now I have to calculate for each expense the portion of the expense that applies to each month of the year (each month being a column in my Expenses table)... again it works, but I would prefer not to have to bring all the dates from all my summary pages into my Expenses table (also I only referred to the "Expenses" table to simplify, but I have a few other tables that I take into account): it just feels messy.

If you have any idea of how to fix the formula itself, that would really help!

Many thanks,
Larry

 

[Fluff]

Cross posted https://www.excelforum.com/excel-fo...-going-wrong-for-some-reason.html#post4974081

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[cohlar]

Hello,

I did cross-post indeed to maximize my chances to get help - apologies that I did not mention it, you are right it makes sense to do so, I'll make sure I do so next time.
I have attached a "light" version of my spreadsheet in the hope that it will help you help me. Please note that I have also done so in the other forum so you can also be up-to-date if I get any answer from there.

Thanks again for your help.

Cheers,
Larry