SUMIF Functions- how to sumup month and specific year and how to get it to sum with blanks

I am trying to create a sheet that will sum all of the pieces of art that I need for a particular month and year from multiple sheets to get to a portfolio view by month. The formula I put in doesn't identify the month by year. That is my first issue. My second issue is that sometimes I have blank cells or cells that say N/A because the criteria doesn't apply to that particular artwork wave. When I have a blank, it returns #invaliddata. How do I change my formula to ignore these blank cells?

To break down this formula, the first chunk of this formula is pointing to a range of data with the dates of when artwork will be complete, the second chunk of this formula is the criteria which is month which is in one of the columns on my spreadsheet, the third chunk is pointing to a range of data with the number of POAs in a project.

SUMIF({High Level Artwork Status: FK DATE}, MONTH(@cell) = [Primary Column]@row, {High Level Artwork Status: POA Count})

Find more posts tagged with

Accepted answers

All comments

Garrett Henke

@Mina S how many sheets are you trying to pull data from for this formula?

Jeff Reisman

@Mina S

The nice thing about Smartsheet is that you can so easily use formulas to create the data you need to match things up.

The first thing I would do, to eliminate the issues with it not knowing the years, is to create a helper column in your High Level Artwork Status sheet called FKHelpDate. Use the VALUE, JOIN, YEAR, and MONTH functions to create a value representing the Year and Month of the FK DATE. Using JOIN converts everything to text, so that we can bring the values together without adding them together, then VALUE turns the result back into a number. For a FK DATE value in this month, this formula would yield "20222"

=VALUE(JOIN(YEAR([FK DATE]@row)) + JOIN(MONTH([FK DATE]@row)))

Do the same thing with the Year/Month you are trying to compare it to from your other sheet.

=SUMIF({High Level Artwork Status: FKHelpDate Column} = [HelperDateColumn]@row, {High Level Artwork Status: POA Count})

Now, in order to not show an error for blank date rows, wrap the whole thing in IFERROR:

=IFERROR(SUMIF({High Level Artwork Status: FKHelpDate Column} = [HelperDateColumn]@row, {High Level Artwork Status: POA Count}), "")

This will leave the cell empty if there's an error, instead of showing the error message. Best to leave the IFERROR out until you're 100% sure your formula is working!

Mina S

Hi @Garrett Henke,

I am trying to pull data from 6 sheets

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.