Need help with sumifs statement
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.
Best Answer
-
Try changing to use the CONTAINS function:
=SUMIFS({balance}, {combined}, CONTAINS("allen wilkerson",@cell), {invoice date}, <TODAY())
Answers
-
Simplest way would be to add an extra column that combines all your email columns and then just use that as your reference
-
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.
-
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?
-
Try changing to use the CONTAINS function:
=SUMIFS({balance}, {combined}, CONTAINS("allen wilkerson",@cell), {invoice date}, <TODAY())
-
that appears to work. Thanks
-
@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!
-
Thanks
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!