Attempting a formula with multiple criteria

Sally Osborne

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 

 

Shaine Greenwood

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

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

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

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

 

J. Craig Williams

=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