How do I pull a count from a column by two other factors from one smartsheet to another sheet?

I'm trying to pull the number of publications that have been published by year, where the data live in three different columns. I want to pull that data to a different sheet. The formula I'm using is this: =COUNTIFS({R&D Presentations and Publications Range 1}, @cell = "Publication", {R&D Presentations and Publications Range 2}, @cell = "Published", {R&D Presentations and Publications Range 3}, IFERROR(YEAR(@cell), 0) = 2021). This at least has gotten me closer to a count, which shows as 0, and is incorrect - it should be 5.

I've gone back and forth with smartsheet team members without success. Does anyone know how to fix this?

Thanks,

Carrie

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

You will need to use two separate COUNTIFS and add them together.

=COUNTIFS({R&D Presentations and Publications Range 1}, @cell = "Publication", {R&D Presentations and Publications Range 3}, IFERROR(YEAR(@cell), 0) = 2021) + COUNTIFS({R&D Presentations and Publications Range 2}, @cell = "Publication", {R&D Presentations and Publications Range 3}, IFERROR(YEAR(@cell), 0) = 2021)

carben27

Hi Paul, Unfortunately, that isn't working either. Now I see the unparseable error again.

carben27

I looked more closely and there was a period in my formula that didn't belong. Now it's showing as 0 again.

Paul Newcome

Try applying a filter to the sheet based on the first "Publication" column and the year being 2021. Once you see how many populate for that, do the same filter but for the second "Publication" column and see how many populate for that one.

carben27

Using filters in the reference sheet shows 4 publications that have been published in 2021. The sheet pulling the data still shows 0. If I filter the sheet that had the data pulled into it, everything disappears because the count is 0. What am I missing?

Paul Newcome

Are you working on 3 different sheets?

carben27

No, I'm pulling info from three columns in a reference sheet over to a new sheet. The columns are: Year, Type, Status.

Paul Newcome

Ok. SO what data is present in each of those columns? I assume the Year column is a date?

carben27

Year is the year, going back to 2015. Status is one of the following dropdown options (or some are missing data): Planned, submitted, accepted, published, abandoned, awarded. I'm looking for published items. The third category is Type, which has the following drop down options: Media Release, Poster, Publication, Platform, Award. I'm looking for publications. Does that help?

Paul Newcome

Ah. Ok. Try this...

=COUNTIFS({Year Column}, @cell = 2021, {Status Column}, @cell = "published", {Type Column}, @cell = "Publication")

Depending on how the Year column is populated, you may need to wrap the criteria in quotes:

=COUNTIFS({Year Column}, @cell = "2021", {Status Column}, @cell = "published", {Type Column}, @cell = "Publication")

carben27

Thank you, Paul, the first one seemed to work!! I'm so excited - thank you for sticking with me on this. I appreciate your time. Carrie

Paul Newcome

Happy to help. 👍️

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.