#### 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
edited 12/09/19

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

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

• ✭✭✭✭✭✭
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"

• ✭✭✭✭✭✭
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"

• Options

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

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