Need help with sumifs statement

Ron Anderson

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.

Leibel S

@Ron Anderson

Try changing to use the CONTAINS function:

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

Leibel S

@Ron Anderson

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

Paul Newcome

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.

Ron Anderson

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?

Leibel S

@Ron Anderson

Try changing to use the CONTAINS function:

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

Ron Anderson

that appears to work. Thanks

Paul Newcome

@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!

Ron Anderson

Thanks