SUMIF stopped working

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!

Find more posts tagged with

Formulas

Accepted answers

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:

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

All comments

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:

Save your sheet as a new sheet
Remove (delete rows) most of the data form this new copied sheet, just leave 5 entries and change their info to generic.
Share this sheet to my email: smartsheetpolska@gmail.com
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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.