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

Cole Frank

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

Amber Eakin

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)))

https://help.smartsheet.com/function/date

I hope that helps!

Cole Frank

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

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.

Cole Frank

Thank you so much