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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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)))

    thinkspi.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would need something along these lines:

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

    thinkspi.com

  • 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
    Paul Newcome ✭✭✭✭✭✭
    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)))

    thinkspi.com

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

    Have a great rest of your day!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help.

    thinkspi.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Hi @MeganJF<\/p>

I guess you forget to use AND( ).<\/p>

For example, <\/p>