# Need help with sumifs statement

Options
✭✭✭✭
edited 10/24/22

I am trying to sum a column in a sheet if one of seven cells contain a matching text string. It is bombing out and I am stuck. The last criteria is to limit the invoice amounts summed to those that are overdue (date before today).

Formula

=SUMIFS({balance}, {A1}, HAS(@cell, "alia.bostaji@colliers.com"), OR({a2}, HAS(@cell, "alia.bostaji@colliers.com")), OR({a3}, HAS(@cell, "alia.bostaji@colliers.com")), OR({a4}, HAS(@cell, "alia.bostaji@colliers.com")), OR({a5}, HAS(@cell, "alia.bostaji@colliers.com")), OR({a6}, HAS(@cell, "alia.bostaji@colliers.com")), {a7}, {invoice date}, <TODAY())

Note, A1=agent 1 email, A2 = agent 2 email, etc.

• ✭✭✭✭✭✭
Options

Try changing to use the CONTAINS function:

=SUMIFS({balance}, {combined}, CONTAINS("allen wilkerson",@cell), {invoice date}, <TODAY())

• ✭✭✭✭✭✭
Options

Simplest way would be to add an extra column that combines all your email columns and then just use that as your reference

• ✭✭✭✭✭✭
Options

First you are going to want to get rid of those errors in the Agent Email columns. Then you will need to create a helper column (that can be hidden after setting everything up) that is of the multi-select dropdown type and use this formula to pull together all of the emails excluding blanks:

=JOIN(COLLECT([Agent 1 Email]@row:[Agent 7 Email]@row, [Agent 1 Email]@row:[Agent 7 Email]@row, @cell <> "", CHAR(10))

Finally you would run your SUMIFS on this single helper column with the HAS function for the criteria.

• ✭✭✭✭
Options

That got me a little closer. I couldn't join the contact columns so instead I concatenated the name columns into a single column. Then I rewrote the sumifs to look at the concatenated column.

=SUMIFS({balance}, {combined}, HAS(@cell, "allen wilkerson"), {invoice date}, <TODAY())

This is a valid statement but is not returning the correct results. It is returning \$81. See screen shot of the invoice table filtered for all invoices due before today that HAS "Allen Wilkerson". Thoughts? Also, I have another column with the names in the sheet where the sumifs statement is collecting data. Is there a way to use that column to compare in the HAS statement?

• ✭✭✭✭✭✭
Options

Try changing to use the CONTAINS function:

=SUMIFS({balance}, {combined}, CONTAINS("allen wilkerson",@cell), {invoice date}, <TODAY())

• ✭✭✭✭
Options

that appears to work. Thanks

• ✭✭✭✭✭✭
Options

@Ron Anderson Yes. You would need the CONTAINS function. The HAS function would have been if you had used the multi-select dropdown for your join.

@Leibel Shuchat Thanks!

• ✭✭✭✭
Options

Thanks

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!