SUMIFS with multiple criterion

Hi there,

I am running the current formula...

=SUMIFS({Sales Range 1}, {Sales Range 2}, [Primary Column]$21, {Sales Range 3}, [Primary Column]@row)

...This formula has a reference point of a sales persons name at "[Primary Column]$21" and the month I want to measure as "[Primary Column]@row". Now I am trying to run this formula to calculate the performance of a team, so multiple sales people in one hit, and I have the sales peoples names in "[Primary Column]$21:[Primary Column]$26".

I have tried to use the formula...

=SUMIFS({Sales Range 1}, {Sales Range 2}, [Primary Column]$21:[Primary Column]$26, {Sales Range 3}, [Primary Column]@row)

...But that doesn't pull any results, as I'm guessing it's trying to find all 6 names at once to them sum it up. How would I put together a formula to achieve this?

Many thanks,



  • SmartLew
    SmartLew ✭✭✭✭

    Instead of using the range, put the individual cells into an OR statement:

    =SUMIFS({Sales Range 1}, {Sales Range 2}, OR(@cell=[Primary Column]$21,@cell=[Primary Column]$22,@cell=[Primary Column]$23,@cell=[Primary Column]$24,@cell=[Primary Column]$25,@cell=[Primary Column]$26),{Sales Range 3}, [Primary Column]@row)

    I'm passionate about helping you leverage the truly awesome power of smartsheet!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!