=SUMIFS, Please help..Formular works on a business collages sheet....

Graeme Baker

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()))))

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

 

Graeme Baker

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

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

 

Likely there is a blank date in your range.

 

Craig