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
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
-
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.
Β
-
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"
-
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"
-
Trying to get the same output. Any luck solving this?
Β
-
=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
