Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Attempting a formula with multiple criteria

Options
Sally Osborne
edited 12/09/19 in Archived 2017 Posts

Hi, can anyone help with the formula for the following required outcomes?

  • $0 if the management agreement has not started [Mgnt Start Date]
  • Equal to the chargeable amount [chargeable amt]  if the Management agreement has started ie [Mgnt Start Date] in the prior month
  • Equal to the previous month [Jan 17] if the agreement has started and the anniversary is not in this month.[Anniversary mth]
  • Equal to the previous month + a percentage increase [% increase]  if the management agreement anniversary is in this month

Thanks 

 

Comments

  • Shaine Greenwood
    Options

    Hello Sally,

    You'd want to make a Nested IF statement that will return the values you need. An example is below, you may need to tweak this to best fit your needs:

    =IF([Mgnt Start Date]1 < TODAY(), 0, IF(AND(MONTH([Mgnt Start Date]1) = (MONTH(TODAY()) - 1), YEAR([Mgnt Start Date]1) = YEAR(TODAY()), [chargeable amt]1, IF(AND(MONTH([Anniversary mth]1) <> MONTH(TODAY()), YEAR([Mgnt Start Date]1) < YEAR(TODAY())), [Jan 17]1, IF(AND(MONTH([Anniversary mth]1) = MONTH(TODAY()), YEAR([Mgnt Start Date]1) < YEAR(TODAY())), [Jan 17]1 * (1 + [% increase])))))

    I'm assuming that your "Anniversary mth" column is a date column on this.

    If you receive an #UNPARSEABLE error, you may need to fix some parenthesis that I could've misplaced.

     

  • Nicole Hodges
    Nicole Hodges ✭✭✭✭✭✭
    Options

    Hello!

    I want to create a formula to total up 2 criteria (must meet BOTH). I want to count if a column has been checked and also is assigned to the central region. Here's the formula I've been working with but with no luck. Any help here please? :)

    =COUNTIFS([SCD Review Complete]:[SCD Review Complete], 1) AND COUNTIFS([Region]:[Region], "Central"

  • Nicole Hodges
    Nicole Hodges ✭✭✭✭✭✭
    Options

    Hello!

    I want to create a formula to total up 2 criteria (must meet BOTH). I want to count if a column has been checked and also is assigned to the central region. Here's the formula I've been working with but with no luck. Any help here please? :)

    =COUNTIFS([SCD Review Complete]:[SCD Review Complete], 1) AND COUNTIFS([Region]:[Region], "Central"

  • dbeaver
    Options

    Trying to get the same output. Any luck solving this?

     

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    =COUNTIFS([SCD Review Complete]:[SCD Review Complete], 1, [Region]:[Region], "Central")

    should work.

    COUNTIFS takes multiple range,criterion pairs. ALL must true to increment the count. 

    All ranges must be of the same size.

    I hope that helps.

    Craig

This discussion has been closed.