Formula Support

NCookBT83

I am attempting to use a formula between two sheets. First sheet is a list of all charges from several service lines. The second sheet is list of all services provided to clients under one service line. I want to create a formula that would look 3 factors and tell me if the charge was posted or not. I believe I could use an IFERROR type formula, but I am not sure where to begin. Any support would be greatly appreciated. Other challenges is that that there could be multiple charges put through for the same client. I am working on adding another type field to add another deciding factor but not sure if that would make a difference.

Jason Tarpinian

Without a little more info, it's hard to build out the formula exactly, but it sounds like you might want to use the COLLECT() either with with a COUNT() or SUM() function. COLLECT() allows you to customize a range to be used within other functions - essentially allowing you to count/sum/match against multiple criteria.

https://help.smartsheet.com/function/collect

Jason Tarpinian

Without a little more info, it's hard to build out the formula exactly, but it sounds like you might want to use the COLLECT() either with with a COUNT() or SUM() function. COLLECT() allows you to customize a range to be used within other functions - essentially allowing you to count/sum/match against multiple criteria.

https://help.smartsheet.com/function/collect

NCookBT83

This is the current formula I started putting together.... keeping in mind that I am very limited in my experience with formulas, I am starting to get somewhere but am stuck now.

=IFERROR(INDEX(COLLECT(({Client ID}, [Client ID]@row, {Charge Type}, [Charge Type]@row, {Actual Charge Amount}, [Federal Filing Charge]@row) "Yes"), "No")

Any idea if I am heading down an incorrect path that would be successful in giving me a "Yes" or "No"? Or am I pretty far off.

Andrée Starå

Hi @NCookBT83

I hope you're well and safe!

To add to Jason's excellent advice/answer.

Try something like this.

=IFERROR(INDEX(COLLECT({Client ID}, [Client ID]@row, {Charge Type}, [Charge Type]@row, {Actual Charge Amount}, [Federal Filing Charge]@row) "Yes"), "No")

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

NCookBT83

I tried...

=IFERROR(INDEX(COLLECT({Client ID}, [Client ID]@row, {Charges - Charge Type}, [Charge Type]@row, {Charges - Actual Charge Amount}, [Federal Filing Charge]@row) "Yes") "No")

Unfortunately, still not working... I feel so close. Any additional thoughts or input?