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))