Formula to Sum a section within certain Dates

Cathi R

I have started a formula to sum the totals between dates and add them to another sheet.

=SUMIF({SJC18 January Tracker Range 1}, IF(AND(Date@row >= DATE(2022, 1, 1), Date@row <= DATE(2022, 1, 31))

The formula is not working. I need to Sum the total of the column between Jan 1, 2022 to Jan 31, 2022 only and add it to another sheet.

Any ideas??

Kelly Moore

Hey @Cathi R

There are a few syntax opportunities with the formula above.

The SUMIF function works when there is only one criteria. The formula above has two criteria. The SUMIFS (plural) function works for any number of criteria - including when there is only one criteria. For this reason, my personal preference is to only use the SUMIFs function.

The syntax of SUMIFS is (range to be summed, range1, criteria1, range2, criteria2, etc). It appears the second range (the date range) is missing. I am assuming that the Totals you are tracking is something other than dates? If you don't have a specific date column listed, you can use the system generated Created column.

Assuming the Totals value being tracked is in Range 1 (as a good practice, rename cross sheet references to reflect the column being used- this will help you and others troubleshoot formulas), your formula could look something like this. You will need to create the Date range yourself, you cannot simply copy paste this formula

=SUMIFS({SJC18 January Tracker Range 1}, {SJC18 January Tracker Date}, AND(@cell >= DATE(2022, 1, 1), @cell <= DATE(2022, 1, 31)))

As an alternative, you could use the Month and Year function to simplify month to month editing.

=SUMIFS({SJC18 January Tracker Range 1}, {SJC18 January Tracker Date}, AND(@cell >= YEAR(@cell)=2022, MONTH(@cell)=1))

Does this work for you?

Kelly

Cathi R

Kelly, Thank you so much. This was very helpful!

Kelly Moore

Hey @Cathi R

There are a few syntax opportunities with the formula above.

The SUMIF function works when there is only one criteria. The formula above has two criteria. The SUMIFS (plural) function works for any number of criteria - including when there is only one criteria. For this reason, my personal preference is to only use the SUMIFs function.

The syntax of SUMIFS is (range to be summed, range1, criteria1, range2, criteria2, etc). It appears the second range (the date range) is missing. I am assuming that the Totals you are tracking is something other than dates? If you don't have a specific date column listed, you can use the system generated Created column.

Assuming the Totals value being tracked is in Range 1 (as a good practice, rename cross sheet references to reflect the column being used- this will help you and others troubleshoot formulas), your formula could look something like this. You will need to create the Date range yourself, you cannot simply copy paste this formula

=SUMIFS({SJC18 January Tracker Range 1}, {SJC18 January Tracker Date}, AND(@cell >= DATE(2022, 1, 1), @cell <= DATE(2022, 1, 31)))

As an alternative, you could use the Month and Year function to simplify month to month editing.

=SUMIFS({SJC18 January Tracker Range 1}, {SJC18 January Tracker Date}, AND(@cell >= YEAR(@cell)=2022, MONTH(@cell)=1))

Does this work for you?

Kelly

Cathi R

Kelly, Thank you so much. This was very helpful!