How to use SUMIF and the criteria is a range of values

Options

Hi Everyone!

Can anyone help me with that. I'm trying to use sumif function for a range of values. for example: I want to sum a values in a column if they range between 50 and 60. How can I do that?

I used: = SUMIF( mycolumn, AND(mycolumn >50 , mycolumn <=60)) but its not working. Thank you

Best Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Taima El Frieh ,

    Try:

    = SUMIF(mycolumn:mycolumn, AND(@cell >50, @cell<=60))

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Taima El Frieh
    Answer ✓
    Options

    Thank you! it did work.

    If I want to add another criteria, I want the formula to check if a specific column has a row that is not blank, then it will add values in another column

    I tried:

    =SUMIFS({referenced column from another sheet}, {another referenced column from another sheet}, NOT(ISBLANK(@row))

    Do you have any thoughts about this? Thank you.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Taima El Frieh ,

    For your formula to work the range {referenced column from another sheet} column needs to be numbers and can't contain any errors. The range {another referenced column from another sheet} column can be text, date, contact, or symbol but not a check box.

    =SUMIFS({referenced column from another sheet}, {another referenced column from another sheet}, NOT(ISBLANK(@row))

    Work for you?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Taima El Frieh ,

    Try:

    = SUMIF(mycolumn:mycolumn, AND(@cell >50, @cell<=60))

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Taima El Frieh
    Answer ✓
    Options

    Thank you! it did work.

    If I want to add another criteria, I want the formula to check if a specific column has a row that is not blank, then it will add values in another column

    I tried:

    =SUMIFS({referenced column from another sheet}, {another referenced column from another sheet}, NOT(ISBLANK(@row))

    Do you have any thoughts about this? Thank you.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Taima El Frieh ,

    For your formula to work the range {referenced column from another sheet} column needs to be numbers and can't contain any errors. The range {another referenced column from another sheet} column can be text, date, contact, or symbol but not a check box.

    =SUMIFS({referenced column from another sheet}, {another referenced column from another sheet}, NOT(ISBLANK(@row))

    Work for you?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Taima El Frieh
    Options

    That worked for me! Thanks.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Glad to help. Thank you for using the Community.

    Be Well,

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!