CODEWITHSUNDEEP
google-sheets
Daniel K
Daniel K

Reputation: 13

Google Sheets - SUMIFS using date ranges as criteria shows wrong values

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)

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.

Upvotes: 1

Views: 364

Answers (1)

Kessy
Kessy

Reputation: 1994

Changing the dates on the months that don't coincide solves the issue.

For example for May:

February 2022 had 28 days but on the function for May you used the 31st of february:

=SUMIFS(input!G3:G200;input!M3:M200;">"&DATE(**2022;2;31**);input!M3:M200;">"&DATE(2022;4;1))

and the result is: Table wrong

If instead I change the March function to: SUMIFS(input!G3:G200;input!M3:M200;">"&DATE(2022;2;28);input!M3:M200;"<"&DATE(2022;4;1))

Table wrigt

The result gets corrected as it should.

Upvotes: 1

Related Questions