# Help with a Monster Formula

Options
Overachievers

Hi everyone! Hope you are all well and somewhat sane :)

I need help with this formula and I'll explain the meaning:

=COUNTIFS([Issuance Date]:[Issuance Date], <=MonthHelper\$1, [Cardmember Status]:[Cardmember Status], "ACTIVE ACCOUNT", [Months Used]:[Months Used], >=3, [Months Used]:[Months Used], <=6, Tenure:Tenure, 12) + COUNTIFS([Issuance Date]:[Issuance Date], <=MonthHelper\$1, [Cancel Date]:[Cancel Date], >=MonthHelper\$1, [Cardmember Status]:[Cardmember Status], <>"ACTIVE ACCOUNT", [Months Used]:[Months Used], >=3, [Months Used]:[Months Used], <=6, Tenure:Tenure, 12)

The end result is how many times an Active Cardholder that has had their card for at least 12 months has used their card for the month of January. In this case, it is for our Medium Users column (3-6 times). The Issuance Date is the date when their card was issued (date formated). The MonthHelper\$1 is 1/31/20 (date formated). Months used column is literally how many months they've used the card. Cancel Date is when this card was/will be cancelled.

I translated this from an excel formula. It pulls the correct numbers, but when I change the MonthHelper to 2/29/20 the numbers stay the same. It keeps the January data. I even tried it for March when there is no March data. It pulled January's numbers.

I've worked on this every day this week and cannot figure out what is the reason. It works perfectly in excel but not Smartsheet.

Darla Brown

What you meditate on, you empower!

Overachiever - Core Product Certified - Mobilizer - EAP

• ✭✭✭✭✭✭
Options

Are you updating the cell in the top row of the column?

• Overachievers
Options

no.

Darla Brown

What you meditate on, you empower!

Overachiever - Core Product Certified - Mobilizer - EAP

• ✭✭✭✭✭✭
Options

That's the problem then. Your formula has a locked in reference to row 1 of the MonthHelper column.

MonthHelper\$1

Adjust that to reference the correct row, and you should be sailing smooth.

• Overachievers
Options

Actually, no. I've tried it with and without the \$. Still pulls January data into February and March

Darla Brown

What you meditate on, you empower!

Overachiever - Core Product Certified - Mobilizer - EAP

• ✭✭✭✭✭✭
Options

You will also nee dto update the row number accordingly to point at the correct cell.

• Overachievers
Options

I don't see my response, so I will post again:

I have for February, MonthHelper2 and March MonthHelper3. It should be pointing to the correct month for pull.

Darla Brown

What you meditate on, you empower!

Overachiever - Core Product Certified - Mobilizer - EAP

• ✭✭✭✭✭✭
Options

Is MonthHelper set as a date type column?

• Overachievers
Options

Yes

Darla Brown

What you meditate on, you empower!

Overachiever - Core Product Certified - Mobilizer - EAP

• ✭✭✭✭✭✭
Options

Are you able to provide a screenshot that shows the row numbers and the MonthHelper column?

Sensitive/confidential data should be hidden.

• Overachievers
Options

Darla Brown

What you meditate on, you empower!

Overachiever - Core Product Certified - Mobilizer - EAP

• ✭✭✭✭✭✭
Options

I think I may see something...

You are using two different COUNTIFS and adding them together.

In the first one you only state that the [Issuance Date] needs to be less than the MonthHelper date specified in your cell reference. So all rows that are counted for January for that COUNTIFS are also going to be counted for February, March, April, May, etc.

You may want to double check your data to see if it is in fact following the logic you have written out in your formulas. It may be that your formula logic just needs some tweaking.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!