Google-sheets – How to calculate a sum based on a date range

formulasgoogle sheets

I am trying to find dates within 30 days in Column B. If the date matches the range it should sum all those that match in column I.

I have tried this formula:

=SUMIF(B:B,">="&TODAY()-30,I:I)

It won't work in Google Sheets. I tried moving the date outside but that didn't work either.

Best Answer

You should use the DATEVALUE function:

=SUMIF(B:B,">"&DATEVALUE(TODAY()-30),I:I)

As an alternative you could place the date =(TODAY()-30) in a cell (eg cell C1) and use the formula

=SUMIF(B:B,">"&C1,I:I)

Functions used:

Related Solutions

Google Sheets – How to Sum Values in a Range of Dates

This works, see F35 in your sheet:

SUMIF(E2:E33;">="&E35;B2:B33)-SUMIF(E2:E33;"<="&E36;B2:B33)

column E is done by calculating a value for the dates to be able to compare them (=VALUE(E1))

Note that separating the args for some reasons is done with ;

Google-sheets – How to use DCOUNTA and get the month from a column instead of the entire date in the criteria

You need to use regexreplace first to remove the time part of the time stamp, and then the Text() function will properly transform the way you want.

=TEXT(REGEXREPLACE("date timestamp string","\d+:\d+:\d+",""),"MM")

Note: the timestamp part of the formula - may need to be altered depending on what exactly your string looks like, if you post an example of the string your starting with - I can modify the regex for you to fit that criteria

ALso if you share an example sheet - we can incorporate your DCOUNTA or a COUNTIF alternative

Best Answer

Related Solutions

Google Sheets – How to Sum Values in a Range of Dates

Google-sheets – How to use DCOUNTA and get the month from a column instead of the entire date in the criteria

Related Topic