Search code examples
dategoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query

Totaling multiple lines into once cell with different date formats

I want to search through a column of dates in the format YYYY-MM-DD (column G - in a random order) and sum up all corresponding cost values for all dates in the same month.

sheets

So, for example, the total cost for December 2019 would be 200.

My current formula is:

=SUMPRODUCT((MONTH(G2:G6)=12)*(YEAR(G2:G6)=2019)*(H2:H6))

This gives me the total cost for that month correctly, but I cannot work out how to do this without hardcoding the year and month!

How would I do this with a formula (given the two date columns are a different format)?

Solution

  • You can do this easily combining SUMIFS with EDATE:

    enter image description here

    SUMIFS function

    EDATE function

    The formula I've used in cell B2 is:

    =SUMIFS($F$2:$F$6;$E$2:$E$6;">="&A2;$E$2:$E$6;"<="&(EDATE(A2;1)-1))

    For this formula to work, in column A must be first day of each month!. In cell A2 the value is 01/11/2019, but applied a format of mmmm yyyy to see it like that (and chart will do the same).