So i want to know how can i sum up purchase requests based on dates
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
-
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
-
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
-
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
-
Thank you so much
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!