Search code examples
excelfunctionlibreofficesumifslibreoffice-calc

Excel/ Libreoffice Calc - determine date range and sum up only the values of the months from that range

How can you sum up only the values ​​of the months that lie in the date range with Sumif? Then I subtract this sum from the initial value and get the rest - see document and screenshot
enter image description here

https://www.libreoffice-forum.de/viewtopic.php?f=6&t=36182

Solution

  • You can try to construct a formula based on SUMIFS() - it would be convenient for conditions "exact equality", but for calculating conditions like "between month1 and month2" the formula becomes rather cumbersome. Therefore it is better to use SUMPRODUCT(). In this case, the formula might look like this:

    =SUMPRODUCT($E3:$P3;COLUMN($E3:$P3)-3>MONTH($C3);COLUMN($E3:$P3)-3<MONTH($D$3))

    This will work well in Calc, but Excel will give erroneous results, zeros. The fact is that Calc easily converts the result of comparing numbers to FALSE and TRUE, realizing that these are 0 and 1. Excel does not want to convert logical values to numbers. Therefore, you will have to additionally wrap each comparison in the function N():

    =SUMPRODUCT($E3:$P3;N(COLUMN($E3:$P3)-3>MONTH($C3));N(COLUMN($E3:$P3)-3<MONTH($D3)))

    This formula will work the same in Calc and Excel.