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

Find more posts tagged with

Formulas

Cell linking

Accepted answers

All comments

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

2023-03-02 12_01_55-Embedded Systems Purchase Request - Smartsheet.com.png

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

2023-03-02 12_04_52-(1) Embedded Systems Purchase Request - Smartsheet.com.png

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

2023-03-02 12_14_35-Firmware Non-labor Budget 2023 - Smartsheet.com.png

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

2023-03-02 12_05_50-(1) 2023 Budget Metric - Smartsheet.com.png

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.