# How to return the first value in a range of values that equals a specific condition

Options

I am trying to develop a formula to display when the last billing was sent based on a series of rows that show the amount that was billed for a specific month.

I have two columns: Billing Month and Billing Amount

I would like the formula to return the last month, in a list of Months (Jan-Dec), where the billing amount is greater than 0.

I.e.

Billing Month Billing Amount

January \$535.28

February \$673.18

March \$1250.25

April \$0.00

May \$10.35

June \$0.00

My hope is to have the formula return the very last month that has been billed. In this case May.

Is this even possible?

• ✭✭✭✭✭✭
Options

I would suggest inserting a helper text/number type column that can later be hidden. In this column, you would assign a number to each of the monthly text values using a nested IF statement.

=IF([Billing Month]@row = "January", 1, IF([Billing Month]@row = "February", 2, IF([Billing Month]@row = "March", 3, ....................................................................................., IF([Billing Month]@row = "December", 12))))))))))))

Then to pull the month, you would use...

=MAX(COLLECT([Helper Column]:[Helper Column], [Billing Amount]:[Billing Amount], @cell > 0))

Finally you would us another nested IF statement to convert the number pulled by the MAX/COLLECT back into the month text.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!