Help with a Monster Formula

Darla Brown

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.

Paul Newcome

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

Darla Brown

no.

Paul Newcome

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.

Darla Brown

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

Paul Newcome

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

Darla Brown

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.

Paul Newcome

Is MonthHelper set as a date type column?

Darla Brown

Yes

Paul Newcome

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

Sensitive/confidential data should be hidden.

Darla Brown

Paul Newcome

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.