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



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.


Best Answer

  • Tamara Purchase
    Tamara Purchase ✭✭✭
    Answer ✓

    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! 😊


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!