So i want to know how can i sum up purchase requests based on dates

Options

I know how to do it with COUNTIF({2023 dates }, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2023))


How do I change this to add the SUM of purchase requests that only falls in quarter 1 of this year, and i can then do it for Q2, Q3, Q4

Answers

  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭
    Options

    Good morning, @Cole Frank ! Do you have a column that indicates the quarter? If not, you could use the DATE function rather than MONTH or YEAR. You might have a formula like this:

    =COUNTIF({2023 dates }, AND(@cell >=DATE(2023,1,1), @cell <= DATE(2023,3,31)))

    I hope that helps!

    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast

  • Cole Frank
    Cole Frank ✭✭✭✭
    edited 03/02/23
    Options

    I just have a column with dates of when the purchase requests were fully approved, and i thought we could say that if the month falls in 1,2,3 to label it as quarter 1


    I want to have this sync up with the budget number and total cost.


    So i can show that these budget lines were purchased during quarter 1, and we used this much of the budget against what was projected when we first created the budget.


    ( I was going to put the formula you all help me within a metrics sheet that I have all the other information from the budget in)

    Column (Actual Completion Date) with dates on the purchase request sheet





    The total price of the PR (In the purchase request sheet)


    The budget number from the budget or i have 15 different columns in the purchase request for each individual line item in a purchase request




    This is the metrics sheet that I use for the dashboard with all the budget information



  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭
    Options

    Okay, I got you. Here's what I'd recommend.

    =SUMIF({Start Date Range}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31)), {total price of the PR})

    The items in brackets {} are cell references, so you'll need to update those with whatever you name them. That would be for Q1. You would just change the dates for Q2-4.

    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast

  • Cole Frank
    Cole Frank ✭✭✭✭
    Options

    Thank you so much

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!