# SUMIFS Formula for Column with Various Possible Returns

Options

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

• ✭✭✭✭✭✭
Answer ✓
Options

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

• ✭✭✭✭✭✭
Options

You would need something along these lines:

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

• Options

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.

• ✭✭✭✭✭✭
Answer ✓
Options

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

• Options

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

Have a great rest of your day!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!