Displaying values in a spreadsheet up to X days away from current date

Question

I'm looking to get some assistance in untangling the mess that is a data entry and summary spreadsheet I've had for the last few years in LibreOffice Calc. The general layout of the spreadsheet's relevant parts are as follows:

Sheet: "Data Entry"

|A    |B    |...  |L    |
|-----|-----|-----|-----|
|Date |Name |...  |Value|

Sheet: "Summary"

|A    |...  |E           |G                       |
|-----|-----|------------|------------------------|
|Name |...  |Total Values|Values from last 90 days|

At the moment, everything works, but I wish to add a new function to the Summary sheet, which is column G. Column E runs a complex formula to search the Data Entry sheet for every row where cells in Summary column A match cells in Data Entry column B. Here is that formula:

=SUMPRODUCT(('Data Entry'.$B$3:INDIRECT("'Data Entry'.B"&'Data Entry'.$A$1+2)=A1)*ISNUMBER('Data Entry'.$L$3:INDIRECT("'Data Entry'.L"&'Data Entry'.$A$1+2)),('Data Entry'.$L$3:INDIRECT("'Data Entry'.L"&'Data Entry'.$A$1+2)))

This is an absolutely confusing mess, I know. I'll attempt to simplify the formula below:

=SUMPRODUCT((B1:B100=A1)*ISNUMBER(L1:L100),(L1:L100))

What I was wondering is how I could modify this formula for Summary column G, where its search would only be limited to the last 90 days.

An example of what I should see:

Sheet: "Data Entry"

|A       |B    |...|L|
|--------|-----|---|-|
|19-08-13|Name1|...|2|
|19-07-25|Name2|...|1|
|19-01-01|Name1|...|3|

Sheet: "Summary"

|A    |...|E|G|
|-----|---|-|-|
|Name1|...|5|2|
|Name2|...|1|1|

EDIT: The following gives me a 502 error:

=SUMPRODUCT(('Data Entry'.$B$3:INDIRECT("'Data Entry'.B"&'Data Entry'.$A$1+2)=A6)*ISNUMBER('Data Entry'.$L$3:INDIRECT("'Data Entry'.L"&'Data Entry'.$A$1+2))*('Data Entry'.A3:"'Data Entry'.A"&'Data Entry'.$A$1+2>TODAY()-91),('Data Entry'.$L$3:INDIRECT("'Data Entry'.L"&'Data Entry'.$A$1+2))*('Data Entry'.A3:"'Data Entry'.A"&'Data Entry'.$A$1+2>TODAY()-91))

Answer 1

The question describes two issues. One is trying to debug a long formula. Generally, the key to that is to break the formula down to its component pieces and test each piece to see what it's doing. Start by keeping logical chunks intact (i.e., several expressions that work together), to identify which section of the formula isn't working. If the error isn't obvious from that, break the section into its component parts.

Do that by copying and pasting each part, and then adjust the pasted piece to make it a standalone formula (like add the equal sign). In case the problem is unbalanced parentheses, copy the entire expression that contains all of the parentheses, then delete other internal expressions and their parentheses, which will be easier to spot; unbalanced parentheses in what's left may become obvious just from this exercise. Copying and pasting will ensure that the problem will be included in what you test; retyping the formula can fix mistakes, and everything works when you test so it doesn't serve a diagnostic purpose.

The other issue was limiting the results to the last 90 days. This can be done similar to how the formula already limits results to the matching name -- add a test to the list of expressions in SUMPRODUCT. To keep things simple, I'll leave out the complexity of using INDIRECT to build the ranges and just show fixed ranges. In context, the additional argument could look like this:

=SUMPRODUCT( ... (L1:L100), (A1:A100>TODAY()-91) ... )

The (L1:L100) is just to show location in the simplified example in the question.

Dates are stored as day counts, so values are in units of days. You can directly add or subtract days. The expression >TODAY()-91 tests for dates older than today by no more than 90 days. This assumes your data cannot contain future dates, since those would be included if you don't expand the formula to limit that, also. The whole expression is a logical test that returns either TRUE (1) or FALSE (0). SUMPRODUCT multiplies the rest of the array result by those values, resulting in either a zero or whatever is the result of the other arguments in the SUMPRODUCT.