Hi, I'm trying to reference a cell that contains a dollar amount based on a date range.

This is what I'm trying (unsuccessfully to accomplish)

=IF([DATE SUBMITTED]@row >= DATE(2022, 12, 1), [DATE SUBMITTED]@row <= DATE(2022, 12, 28), "PO Amount Including Change Orders"

Looking for the amount in "PO Amount Including Change Orders" to show up if the cell "Date Submitted" falls within the date span.

Any ideas?

Thanks!

Find more posts tagged with

Sharing and Collaboration

Formulas

functionality

Accepted answers

Paul Newcome

Try this:

=IF(AND([DATE SUBMITTED]@row >= DATE(2022, 12, 1), [DATE SUBMITTED]@row <= DATE(2022, 12, 28)), [PO Amount Including Change Orders]@row)

All comments

Paul Newcome

Try this:

=IF(AND([DATE SUBMITTED]@row >= DATE(2022, 12, 1), [DATE SUBMITTED]@row <= DATE(2022, 12, 28)), [PO Amount Including Change Orders]@row)

Rover4679

Thanks for the quick response! Its still gives me the unparseable error.

Rover4679

Never mind, I missed a colon! Working fine and thanks!

Paul Newcome

Happy to help. 👍️

Rover4679

OK, I need to take it one step further. I have the following formula that works fine but does not give the information I need.

=IF(Branch@row = "Prairieville", (IF(AND([PO Entered Date]@row >= DATE(2023, 1, 1), [PO Entered Date]@row <= DATE(2023, 1, 31)), SUM([PO Amount (H)]1:[PO Amount (H)]10))))

"PO Amount" is a column of values. I need to only count the values in that column that pertain to the filter of the "Branch" and Date Range, not sum the total of the column. Any ideas?

I can accomplish this using multiple formulas in multiple rows but its extremely cumbersome and sloppy.

Thanks again!

Paul Newcome

You would need to use a SUMIFS which allows you to specify a range to sum along with which cells to include based on range/criteria set(s).

Rover4679

Do you mind giving an example? I can use sumifs to total the entire column, but not selected cells based on the date of another cell.

Thanks!

Paul Newcome

@Rover4679

You would use the standard SUMIFS.

=SUMIFS(range_to_sum, 1st_criteria_range, 1st_criteria, 2nd_criteria_range, 2nd_criteria, ...............................)

=SUMIFS([Sum Column]:[Sum Column], [Date Column]:[Date Column], IFERROR(YEAR(@cell), 0) = 2023, [Branch Column]:[Branch Column], @cell = "Branch Text")

Rover4679

Thanks again for the help!

=IF(Branch@row = "Prairieville", (IF(AND([PO Entered Date]@row >= DATE(2023, 1, 1), [PO Entered Date]@row <= DATE(2023, 1, 31)), SUM([PO Amount (H)]1:[PO Amount (H)]10))))

This one has me confused to a point that I may not be able to accomplish what I'm trying to do. This is the current formula that works correctly for the dates, but it adds all of column "PO Amount". I need it to only sum the amounts for the dates that correspond to the date criteria. This is accomplished with the SUMIFS formula above?

Thanks again and I apologize for not getting this a little easier.

Paul Newcome

Yes. This would be accomplished by the SUMIFS above. You would just update the column names and "branch text" to match what you have in your sheet.

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.