I am creating a financial overview tool in which I want to make sums of income/costs/profit - all summarised based on the month of the year in which those business cases were finished.
I ran into a problem with the SUMIFS formula not showing a correct value. Some months show correct value using a first formula, some only using the second, etc.
Google Spreadsheet Link (all important numbers are randomised)
I tried three options : (February as an example)
=SUMIFS(input!H3:H200;input!M3:M200;">"&DATUM(2022;1;31);input!M3:M200;"<"&DATUM(2022;3;1))=>>This way I wanted to limit the SUMIF by only choosing cells with date > January but < March=SUMIFS(input!H3:H200;input!M3:M200;">"&DATUM(2022;2;0);input!M3:M200;"<"&DATUM(2022;2;31))=>>Limiting the SUMIF by choosing cells with date range higher than 2022/2/0 and lower than 2022/2/32 (unreachable dates)=SUMIFS(input!H3:H200;input!M3:M200;">="&DATUM(2022;2;1);input!M3:M200;"<="&DATUM(2022;2;31))=>>Limiting the SUMIF by choosing cells with date range higher and including 2022/2/1 and lower + including 2022/2/31 (unreachable dates)
Why didnt i just refer to a cell with the sum already calculated? Because the input list is an excel export from our crm platform which is never the same and I want to create something that does the work for me. Why didnt I use index + vlookup to simply find the sum value based on the keyword "month name" + column number? Because I need to use a similar formula as sumifs in the next columns that are not shown yet. If there is no direct solution to my problem then I will resort to using the index+vlookup.
