Welcome!

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

Find more posts tagged with

Comments

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