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

Rover4679
Rover4679 ✭✭✭
edited 12/02/22 in Formulas and Functions

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!

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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
    Rover4679 ✭✭✭

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

  • Rover4679
    Rover4679 ✭✭✭

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

  • Rover4679
    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
    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
    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
    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
    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
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!