#INVALID DATA TYPE issue in SUMIFS formula caused by blank dates

Chris Benskin

I have a SUMIFS formula that I use to calculate monthly dollar amounts from another sheet. I know I'm getting #INVALID DATA TYPE due to blank date columns in the source sheet. I think perhaps an IFERROR formula might take care of the issue but I cant get it formatted correctly.

Current Formula. Returns correct result as long as the date fields in the source sheet are popultaed.

=SUMIFS({Purchasing Log_FM0052 Rev. B Range 2}, {Purchasing Log_FM0052 Rev. B Range 1}, @cell = $Supplier@row, {Purchasing Log_FM0052 Rev. B Range 3}, MONTH(@cell) = 11, {Purchasing Log_FM0052 Rev. B Range 3}, YEAR(@cell) = 2021)

Would the IFERROR work here and if so, how should it be put into this formula?

Kelly Moore

Hey @Chris Benskin

This should work for you

=SUMIFS({Purchasing Log_FM0052 Rev. B Range 2}, {Purchasing Log_FM0052 Rev. B Range 1}, @cell = Supplier@row, {Purchasing Log_FM0052 Rev. B Range 3}, IFERROR(MONTH(@cell),0) = 11, {Purchasing Log_FM0052 Rev. B Range 3}, IFERROR(YEAR(@cell),0) = 2021)

Does this get you what you need?

Kelly

Kelly Moore

Hey @Chris Benskin

This should work for you

=SUMIFS({Purchasing Log_FM0052 Rev. B Range 2}, {Purchasing Log_FM0052 Rev. B Range 1}, @cell = Supplier@row, {Purchasing Log_FM0052 Rev. B Range 3}, IFERROR(MONTH(@cell),0) = 11, {Purchasing Log_FM0052 Rev. B Range 3}, IFERROR(YEAR(@cell),0) = 2021)

Does this get you what you need?

Kelly

Chris Benskin

Kelly

Thank you. Works spot on for what I needed.