Additional Criteria changes result

Question

Hi folks, I'm struggling with a formula that should be simple. When the formula is this, it works great for one of two Types. The second type pulls the wrong Billing Annual Fee. =IFERROR(INDEX(COLLECT({Billing Annual Fee}, {Segment}, Segment@row, {Billing Ownership}, [Billing Ownership]@row), 1), 0) When I modify the formula to this, the second type results in 0. Luckily, it does not change the the results in the first type fields. =IFERROR(INDEX(COLLECT({Billing Annual Fee}, {Segment}, Segment@row, {Billing Ownership}, [Billing Ownership]@row, {Type}, Type@row), 1), 0) I am sure that I'm just tired and missing something incredibly simple, I would appreciate a second set of eyes on this.

Jess D · Answer

Thanks for jumping in to help. It turns out that it was a combination of data error (two of the segments didn't match within the sheet) and typing out the column rather than clicking it.

jmyzk_cloudsmart_jp · Answer

Your formula looks OK. Mine works fine.

=IFERROR(INDEX(COLLECT({Billing Annual Fee}, {Segment}, Segment@row, {Billing Ownership}, [Billing Ownership]@row, {Type}, Type@row), 1), 0)

One thing I notice is some blank space between '[Billing Ownership]@row,' and '{Type}'. But Smartsheet usually handles such blanks.

In the example below, the third row returns 0, as there is no such combination in the referenced sheet.

Segment 2	Ownership 3	Type 3