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

Options
✭✭✭
edited 12/02/22

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!

• ✭✭✭✭✭✭
Options

Try this:

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

• ✭✭✭✭✭✭
Options

Try this:

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

• ✭✭✭
Options

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

• ✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• ✭✭✭
Options

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!

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
Options

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!

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!