Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

SUMIFS - Multiple criteria syntax guidance

Bryan Moss
Bryan Moss ✭✭✭✭
edited 12/09/19 in Archived 2017 Posts

Hello,

 

I am am struggling to get SUMIFS working with multiple criteria values.  I want to sum data based on Month column and then by probability column.  Here are a couple methods I have tried but I cannot seem to figure out what I need to do in order to make it work.

 

=SUMIFS(Month:Month, "March", Probability:Probability, "75%", Revenue:Revenue)

 

=SUMIFS(Month:Month, "March", Revenue:Revenue, Probability:Probability, "75%")

 

Ideally I would love to sum all rows that equal march and greater than 75%.    Secondly I would like to sum if it is 50-75%.  

 

Any ideas?

 

I cannot seem to make this work.  

Comments

  • Bryan Moss
    Bryan Moss ✭✭✭✭

    Small update...  I got past the "invalid argument set" error but I still cannot get it to return a value.  

     

    =SUMIFS(Revenue:Revenue, Month:Month, "March", Probability:Probability, >="75")

     

    =SUMIFS(Revenue:Revenue, Month:Month, "March", Probability:Probability, "75%")

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Bryan,

     

    I assume your [Month] has the text "March" in it and you aren't looking at a date directly.

    I also assume your [Probabilty] column is formatted for percentage.

     

    If those are true, then this formula:

     

    =SUMIFS(Revenue:Revenue, Month:Month, "March", Probability:Probability, >0.75)

     

    will sum the Revenue column for March and probability greater than 75%

     

    and this 

     

    =SUMIFS(Revenue:Revenue, Month:Month, "March", Probability:Probability, >=0.5, Probability:Probability, <=0.75)

     

    will sum the Revenue column for March and probability greater than or equal to 50% and less than or equal to 75%

     

    Hope this helps.

     

    Craig

     

  • Bryan Moss
    Bryan Moss ✭✭✭✭

    Brilliant!  Thank you, that was exactly what I needed to make it work. Cheers!

This discussion has been closed.