#INVALID REF with SUMIFS Formula

Hi Smartsheet Community,

I'm having an issue with a cross-sheet SUMIFS formula that works most of the time, but occasionally shows a #INVALID REF error in the sheet and/or reports. To fix this I need to manually open and save the source sheet.

The goal is to sum the invoiced retainer amount on the INVOICE sheet using three criteria 1) PO#, 2) Status is not Rejected, and 3) the BFMatterRef.

Here's the formula, which lives in my PO Sheet:

=SUMIFS({InvRetainerAmount}, {FirmInvoicePO}, [PO#]@row , {InvoiceStatus}, "<>Rejected", {BFMatterRef}, [BF Matter #]@row )

Has anyone experienced a similar issue with cross-sheet formulas not updating automatically when new data is added to the source sheet by an automation? Is there aa recommended workaround for this problem?

Thanks for your help!

Find more posts tagged with

Accepted answers

Paul Newcome

@midori I would suggest reaching out to Support. That is not supposed to be happening.

All comments

jmyzk_cloudsmart_jp

Hi @midori

I first thought that "<>Rejected" was the problem. It should be "<>Rejected"; otherwise, you are checking if the {InvoiceStatus} is "<>Rejected." In that case, the formula should return nothing, since you would not enter a status of "<>Rejected."

[Your Formula]: SUMIFS(InvRetainerAmount, FirmInvoicePO, PO#@row , InvoiceStatus, "<>Rejected", BFMatterRef, Matter #@row )

However, as you can see in the INVOICE sheet, the first row's status is "Approved," which is not "Rejected," so your formula should return nothing in PO sheet. However, it somehow considers this condition true and returns the invoice value.

https://app.smartsheet.com/b/publish?EQBCT=b7978ce7753d4feda77f1732b1f444c9

https://app.smartsheet.com/b/publish?EQBCT=2600acc287a244e1b64bf19f04071818

Another strange phenomenon happens when I leave the status blank (third row).

In this case, since the status is not "Rejected," the formula should return the invoice value. However, my corrected formula returns nothing.

[Just "<>Rejected" Fix] = SUMIFS(InvRetainerAmount, FirmInvoicePO, PO#@row , InvoiceStatus, <>"Rejected", BFMatterRef, Matter #@row )

Therefore, I first added a helper column, [<>"Rejected"], with the following formula:

=InvoiceStatus@row <> "Rejected".

As the sheet shows, the formula checks the condition properly.

"InvAmount with Helper Column" uses the helper column.

[InvAmount with Helper Column] = SUMIFS ({InvRetainerAmount}, {FirmInvoicePO}, [PO#]@row , {Not Rejected}, TRUE, {BFMatterRef}, [Matter #]@row )

Finally, in [InvAmount], I used the OR(ISBLANK(cell), cell <> "Rejected") condition, which gave me the expected results.

[InvAmount] = SUMIFS ( {InvRetainerAmount}, {FirmInvoicePO}, [PO#] @ row , {InvoiceStatus}, OR ( ISBLANK (@ cell ), @ cell <> "Rejected"), {BFMatterRef}, [Matter #] @ row )

I hope this helps you tackle your question.

"InvAmount with Helper Column" uses the helper column.

Paul Newcome

No need for a helper column. That particular error indicates that you have typed in a {Cross Sheet Reference} that does not exist. Click inside of each of your cross sheet references. If the blue link in the formula helper box reads "Reference Another Sheet", that means your {Cross Sheet Reference} was not actually created. In those instances, follow the below instructions to create them.

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

midori

Thank you @Paul Newcome and @jmyzk_cloudsmart_jp for the suggestions. My formula works 80% of the time. Every now and then this error shows up. To resolve, I open the PO sheet (source sheet), save, and it solves the issue. This, however, is unsustainable when others view either a report or a dynamic view.

P.S. other simple formulas created after this one are also show similar errors on reports. For example =IF([Reject Cleared]@row = 0, IF([Invoice Stats]@row = "Rejected", TODAY() - [Invoice Received Date]@row, "")). Here there is no cross referencing. I am calculating the aged date of rejected invoices if they have not been cleared by the Reject Cleared checkbox.

Paul Newcome

@midori I would suggest reaching out to Support. That is not supposed to be happening.

midori

Thank you @Paul Newcome - I'll do that. It at least gives me comfort to know that my formulas are correct.

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.