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, Please help..Formular works on a business collages sheet....

Graeme Baker
edited 12/09/19 in Archived 2017 Posts

Hi Team,

 

This formula works on a business collage sheet, he reffed me to smart sheet and has been helping me build my sheet. this returns #Incorrect Argument Set....whats missing

 

=SUMIF([Value of Jobs]2:[Value of Jobs]60, Result2:Result60, 1, [Sold Date]2:[Sold Date]60, MONTH(@cell) = MONTH(TODAY(), SUMIF([Value of Jobs]2:[Value of Jobs]60, Result2:Result60, 1, [Sold Date]2:[Sold Date]60, YEAR(@cell) = YEAR(TODAY()))))

Comments

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

    Looks like you have a nested SUMIF there.

    What's that for?

     

    Because you have multiple criteria, you need to use the SUMIFS function, not SUMIF.

     

    Are you trying to sum the values in the [Value of Jobs] column (rows 2-60) when:

     

    1. Result = 1 (or checked?)

    2. Sold Date is this month

    3. Sold Date is this year

     

    ?

     

    If so, this:

     

    =SUMIFS([Value of Jobs]2:[Value of Jobs]60, Result2:Result60, 1, [Sold Date]2:[Sold Date]60, MONTH(@cell) = MONTH(TODAY()), [Sold Date]2:[Sold Date]60, YEAR(@cell) = YEAR(TODAY()))

     

    might be what you are looking for.

     

    Note, all of the [Sold Date] cells need to be filled in. You can wrap the formula above in an IFERROR() function.

     

    Craig

     

  • Hi Craig,

     

    Sum values when 1.      Result = 1 (100%)

                                  2&3  Sold Date  (01/03/17)  so both month & year.

     

    Copy and paste your formula into sheet, it retuned #INVALID DATA TYPE.

     

    Where dose the IFERROR() formula go ?

     

    Thanks

     

     

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

    =IFERROR(current formula, "what you want to display if there is an error.")

     

    Likely there is a blank date in your range.

     

    Craig

     

This discussion has been closed.