Compare 2 columns, product type and date and depending on the date, return a number in a 3rd column

Options

I have a working formula that looks at a product type column and depending on the type, inserts a number in another column. The issue I am having is I need to now include comparing the ship date with the product type to return a number. If the ship date is between 06/01/2024 and 08/31/2024 and the product type is A, the number should return 34; if the ship date is not between those dates, the number should return 28. I am unsure what formula to use to look at the 3 columns and return the number. Also this would only be the case for 2 product lines; all the other product lines would not need to look at the ship date so how would I combine 2 formulas to return a number?

Current working formula just to return a number: =IF([PRODUCT TYPE]@row = "A", "30", IF([PRODUCT TYPE]@row = "B", "28", IF([PRODUCT TYPE]@row = "C", "24", IF([PRODUCT TYPE]@row = "D", "30", IF([PRODUCT TYPE]@row = "E", "28", IF([PRODUCT TYPE]@row = "MAXAM1", "28", IF([PRODUCT TYPE]@row = "METRO", "30", IF([PRODUCT TYPE]@row = "F", "32", IF([PRODUCT TYPE]@row = "G", "30", IF([PRODUCT TYPE]@row = "H", "30", IF([PRODUCT TYPE]@row = "I", "30", IF([PRODUCT TYPE]@row = "J", "30", IF([PRODUCT TYPE]@row = "QUATTRO PLYFORM", "30", IF([PRODUCT TYPE]@row = "K", "30", IF([PRODUCT TYPE]@row = "L", "30", IF([PRODUCT TYPE]@row = "M", "30", IF([PRODUCT TYPE]@row = "N", "30")))))))))))))))))

Answers

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Options

    Try this:

    =IF(AND([SHIP DATE]@row >= DATE(2024, 6, 1), [SHIP DATE]@row <= DATE(2024, 8, 31), [PRODUCT TYPE]@row = "A"), 34, IF(AND([PRODUCT TYPE]@row = "A", OR([SHIP DATE]@row > DATE(2024, 8, 31), [SHIP DATE]@row < DATE(2024, 6, 1))), 28, IF([PRODUCT TYPE]@row = "C", 24, IF([PRODUCT TYPE]@row = "F", 32, IF(OR([PRODUCT TYPE]@row = "B", [PRODUCT TYPE]@row = "E", [PRODUCT TYPE]@row = "MAXAM1"), 28, IF(OR([PRODUCT TYPE]@row = "A", [PRODUCT TYPE]@row = "D", [PRODUCT TYPE]@row = "METRO", [PRODUCT TYPE]@row = "G", [PRODUCT TYPE]@row = "H", [PRODUCT TYPE]@row = "I", [PRODUCT TYPE]@row = "J", [PRODUCT TYPE]@row = "QUATTRO PLYFORM", [PRODUCT TYPE]@row = "K", [PRODUCT TYPE]@row = "L", [PRODUCT TYPE]@row = "M", [PRODUCT TYPE]@row = "N"), 30, 28))))))

    I'm sure there's a way to streamline this formula, but this seems to work.

  • Matthew J McAteer
    Options

    Hi @SMB,

    Try this:

    =IF(AND([Product Type]@row = "A", [Ship Date]@row >= DATE(2024, 6, 1), [Ship Date]@row <= (DATE(2024, 8, 31))), "34", IF([Product Type]@row = "A", "28", IF([Product Type]@row = "B", "28", IF([Product Type]@row = "C", "24", IF([Product Type]@row = "D", "30", IF([Product Type]@row = "E", "28", IF([Product Type]@row = "MAXAM1", "28", IF([Product Type]@row = "METRO", "30", IF([Product Type]@row = "F", "32", IF([Product Type]@row = "G", "30", IF([Product Type]@row = "H", "30", IF([Product Type]@row = "I", "30", IF([Product Type]@row = "J", "30", IF([Product Type]@row = "QUATTRO PLYFORM", "30", IF([Product Type]@row = "K", "30", IF([Product Type]@row = "L", "30", IF([Product Type]@row = "M", "30", IF([Product Type]@row = "N", "30"))))))))))))))))))

    You could also try listing all of your Product Types and their Return Numbers on a separate sheet, then use the cross sheet formula function to bring back the values using an INDEX/COLLECT formula, but this should work.

    Hope this helps 😊

    Matthew

  • SMB
    SMB ✭✭
    Options

    Thanks for all the information but I need 3 products to look at the date and all the rest of the products just return a number. These formulas work but only for 1 product that looks at the dates. I want to list the 3 products first that need to compare it to the date followed by the rest that will return a number.

    I don't want to use an INDEX/COLLECT formula but will if I have to. I was thinking that there has to be an OR somewhere in the formula from Matthew such as:

    =IF(AND([Product Type]@row = "A", [Ship Date]@row >= DATE(2024, 6, 1), [Ship Date]@row <= (DATE(2024, 8, 31))), "34", IF([Product Type]@row = "A", OR =IF(AND([Product Type]@row = "B", [Ship Date]@row >= DATE(2024, 6, 1), [Ship Date]@row <= (DATE(2024, 8, 31))), "34", IF([Product Type]@row = "B", ETC....

    What do you think?

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Options

    I don't think it's clear what all of the conditions are you are trying to capture in your descriptions above. Index/Match is one way to go about solving or list out all of the scenarios (if/then/this or then this), it would be easier to piece together the formula you are looking for.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!