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!

Find more posts tagged with

Accepted answers

Paul Newcome

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)))

All comments

Paul Newcome

You would need something along these lines:

=SUMIFS({Range To Sum}, {Range To Evaluate}, CONTAINS("7607", @cell))

BuckeyeGirl72

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.

Paul Newcome

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)))

BuckeyeGirl72

Paul, that worked. Thank you so much for your help ~ I was feeling defeated.

Have a great rest of your day!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.