How can I sum one column based on dates of another column?

LorenOhh · 06/23/23

Hello,

I am trying to sum up the revenue generated during a fiscal year. I have one column titled "Payments Received" and another column titled "Date Received" and would like to tally the totals received for each fiscal year (July 1 thru June 30). I've attached a screenshot of the columns. I have been trying to build a formula using SUMIF but have been unsuccessful.

Tamara Purchase · 06/24/23

Hi LorenOhh!

Try using something like this:

=SUMIFS([Payments Received]1:[Payments Received]16, [Date Received]1:[Date Received]16, OR(AND(MONTH(@cell) >= 7, YEAR(@cell) = 2022), AND(MONTH(@cell) <= 6, YEAR(@cell) = 2023)))

The OR statement includes an AND statement for each half of the fiscal year. The first AND statement represents July-Dec of the first half of the fiscal year (e.g. July 2022 through Dec 2022), and the second AND statement represents Jan-June of the second half of the fiscal year (e.g. Jan 2023 through June 2023). Then you should only need to update the year numbers in that part of the formula for different fiscal years.

Hope this helps! 😊

jmyzk_cloudsmart_jp · 06/24/23

Hi @LorenOhh

Using the helper column, "Year," which gets the year number from the date, you can use SUMIF or SUMIFS more easily.

Formula:

Total:

=SUMIFS([Payments Received]:[Payments Received], Year:Year, [FY3]@row)

Year:

=IFERROR(YEAR([Date Received]@row), 0)

You get the same result using the reports easier, utilizing the report's grouping and summarizing capability.

Please take a look at the demo Dashboard in the following URL.

sum one column based on dates of another column demo DB.png

https://app.smartsheet.com/b/publish?EQBCT=a2543be5a44c496a8c27fc4d19946a96

Tamara Purchase · 06/24/23

Hi LorenOhh!

Try using something like this:

=SUMIFS([Payments Received]1:[Payments Received]16, [Date Received]1:[Date Received]16, OR(AND(MONTH(@cell) >= 7, YEAR(@cell) = 2022), AND(MONTH(@cell) <= 6, YEAR(@cell) = 2023)))

The OR statement includes an AND statement for each half of the fiscal year. The first AND statement represents July-Dec of the first half of the fiscal year (e.g. July 2022 through Dec 2022), and the second AND statement represents Jan-June of the second half of the fiscal year (e.g. Jan 2023 through June 2023). Then you should only need to update the year numbers in that part of the formula for different fiscal years.

Hope this helps! 😊

LorenOhh · 06/26/23

https://community.smartsheet.com/discussion/comment/382140#Comment_382140

Thank you! This worked perfectly.

How can I sum one column based on dates of another column?

Best Answer

Answers

Help Article Resources

Categories