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....
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
-
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
-
=IFERROR(current formula, "what you want to display if there is an error.")
Likely there is a blank date in your range.
Craig
