SUMIF stopped working

Andrea Zenner

This formula was working in my sheet (summary field) and it just stopped. I don't know what I could have changed to make this stop working. I am now getting an invalid data type error.

It's a simple formula! If the year is 2022, sum up the charges column.

My Year column is calculated, YEAR([Date Signed]), is that why?

I tried adding the YEAR([Date Signed]) to the SUMIF formula and I get an unparsable error.

I know I'm missing something simple!

Thanks!

Tomasz Giba

Hi @Andrea Zenner

Thanks for sharing the results!

I testes this formula and it works:

So let me explain the logic here:

=Year() formula converts date to the Number data type

When you enter in your SUMIF formula "2022" (with quotation marks) it means for Smartsheet that this is a text

so "2022" is not equals 2022.

Check the data type of your Year and Charge columns - it should be Text/Number.

If you can't figure this out you can do this so I can inspect your sheet and logic:

1. Save your sheet as a new sheet
2. Remove (delete rows) most of the data form this new copied sheet, just leave 5 entries and change their info to generic.
3. Share this sheet to my email: smartsheetpolska@gmail.com
4. I will check it out and let you know.

Tomasz Giba

Hi @Andrea Zenner

Try to remove quotation marks from SUM IF, just enter 2022.

Let me know if that works?

Thanks!

Andrea Zenner

@Tomasz Giba , that didn't work. I created a new column (just to test) and just typed the year in and then the formula works. So it must be because my year column is a formula.

I tried =SUMIF(YEAR([Date signed]:[Date signed]), "2022", Charge:Charge), so putting the calculation of the year in the formula, and I still get invalid data type.

This should be possible...

Tomasz Giba

Hi @Andrea Zenner

Thanks for sharing the results!

I testes this formula and it works:

So let me explain the logic here:

=Year() formula converts date to the Number data type

When you enter in your SUMIF formula "2022" (with quotation marks) it means for Smartsheet that this is a text

so "2022" is not equals 2022.

Check the data type of your Year and Charge columns - it should be Text/Number.

If you can't figure this out you can do this so I can inspect your sheet and logic:

1. Save your sheet as a new sheet
2. Remove (delete rows) most of the data form this new copied sheet, just leave 5 entries and change their info to generic.
3. Share this sheet to my email: smartsheetpolska@gmail.com
4. I will check it out and let you know.

Paul Newcome

@Andrea Zenner Give this a try...

=SUMIF([Date signed]:[Date signed], IFERROR(YEAR(@cell), 0) = 2022, Charge:Charge)

Andrea Zenner

@Tomasz Giba Thank you! Apparently there is something goofy with that sheet. When I save it as new the formula works. At least I know I can handle a simple formula. :-)

Andrea