I have a series of columns for Forecast and Actual for each month (1 - 12). For past months I want to sum the Actual, but sum the Forecast for the current month and future months. I simply cannot sort the logic or even get to grips with SUMPRODUCT.
Can anyone point me in the right direction?
Attach an example .ods of what you have.
Mention your LibO version and the OS.
My version is:
Version: 7.3.7.2 / LibreOffice Community
Build ID: 30(Build:2)
CPU threads: 20; OS: Linux 6.8; UI render: default; VCL: gtk3
Locale: en-GB (en_GB.UTF-8); UI: en-GB
Ubuntu package version: 1:7.3.7-0ubuntu0.22.04.7
Calc: threaded
The test file is:
Test1.ods (14.9 KB)
And for this example you expect the result to be 47? This number can be obtained using
=SUMPRODUCT(IF($B$1:$AK$1="";0;IF($B$1:$AK$1>=DATE(YEAR(NOW());MONTH(NOW());1);$B$3:$AK$3;$C$3:$AL$3)))
-
Please don’t use “Suggest a solution” when giving additional Information.
Edit your question for the purpose.
Replying to another person’s comment use the “Comment” tool. - The attached example sheet doesn’t contain a contiguous series of months. There are gaps. No explanation given.
- There is no hint where the sum should be diplayed, or how many rows will be needed.
Yes, the number 47 is good. I’ll try your suggestion to see if it works for the rest of the sheet (only 40 rows to check!).
Many thanks JohnSUN.
Oh, please, in this case, remove the dollar sign next to the row number 3 - write $B3:$AK3;$C3:$AL3 instead of $B$3:$AK$3;$C$3:$AL$3
1 Like
Yes, I just did that after copying the calc to the rest of the rows: and it works perfectly - many thanks indeed.
All I have to do now is work out how it works. Thanks