SUMIFS Formula for Column with Various Possible Returns
Hi there! I am a big Excel user switching to Smartsheet and need help with a SUMIFS formula.
In Excel, the formula was:
=SUM(SUMIFS('Outside Counsel Invoices'!$G:$G,'Outside Counsel Invoices'!$M:$M,"FY23",'Outside Counsel Invoices'!$D:$D,{"=7607","=7607*"}))
Since Wildcards (*) are not used in Smartsheet, I am struggling with creating a SUMIFS formula that looks for multiple variations of a number in a column. In the example above, I want the formula to find any variation of 7607 in that column. By variations, sometimes the number will be 7607 or 7607-1 or 7607-2, ect.
Any help you can give is greatly appreciated. Thanks!
Best Answer
-
It looks like you have a mix of text strings and numerical values. Try the below. If that doesn't work then we do have one more option.
SUMIFS({Range To Sum}, {Range To Evaluate}, OR(@cell = 7607, CONTAINS("7607", @cell)))
Answers
-
You would need something along these lines:
=SUMIFS({Range To Sum}, {Range To Evaluate}, CONTAINS("7607", @cell))
-
Thank you Paul for your suggestion. When I added the CONTAINS("7607", @cell) to my formula, it only picked up the totals for any amount with a dash after 7607; example 7607- ~ it didn't pick up the totals for those with 7607; meaning without a dash.
Maybe to better understand, let me provide a little different info. Below is an example of my table in Smartsheet. What I would like to happen is to collect the total amount for every "row" where the account number is 7607 is listed. This includes 7607 AND 7607-2 AND 7607-5.
Account # Amount
7607 $100.00
7632 $200.00
7607-2 $50.00
8016 $5,000.00
7607-5 $500.00
Hopefully, this makes better sense. Again, any help with this request is greatly appreciated.
-
It looks like you have a mix of text strings and numerical values. Try the below. If that doesn't work then we do have one more option.
SUMIFS({Range To Sum}, {Range To Evaluate}, OR(@cell = 7607, CONTAINS("7607", @cell)))
-
Paul, that worked. Thank you so much for your help ~ I was feeling defeated.
Have a great rest of your day!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 405 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!