Help! Cannot get COUNTIFS formula to work

@Paul Newcome I am trying to count the number of occurrences of the "Deliverable Type" with a quarterly date range (i.e., January to March, April-June, July-September, October-December). I have read through and tried following this thread and trying a different combination of formulas suggested by you but apparently I am doing something very wrong. FYI, the data is coming from a reference sheet (see picture) and the date field is formatted as a date.

This is the last formula I tried and it also resulted in the result of #UNPARSABLE as have all of the others I have tried.

=COUNTIFS({Hardee, Highlands Data Range 4}, [Deliverable Type]=[T2: Training Parents, Teachers, School Personnel, and Community interagency, fairs, etc], AND([Date of Service]:[Date of Service], AND(IFERROR(MONTH(@cell), 0) = 7, IFERROR(YEAR(@cell), 0) = 2022), AND((IFERROR(MONTH(@cell), 0) = 9, IFERROR(YEAR(@cell), 0) = 2022))

The reference fields I am trying to count and range I am trying to use are highlighted. Eventually I would like it to pull this info same from a total of 5 pages to give me a combined total for the quarter. Please let me know if you need any other information! Thank you!!!

Find more posts tagged with

Accepted answers

Paul Newcome

Are you wanting to add the results together from all of the sheets? If so, you would create separate COUNTIFS (one for each sheet) and then add them together.

=COUNTIFS(.....) + COUNTIFS(.....)

All comments

Paul Newcome

You would need to create separate cross sheet references for each column that you want to evaluate. Then to fix the syntax issues it would look more along the lines of...

=COUNTIFS({Deliverable Column Range}, @cell = "Text of Choice", {Date Column Range}, AND(IFERROR(MONTH(@cell), 0)>= 1, IFERROR(MONTH(@cell), 0)<= 3, IFERROR(YEAR(@cell), 0) = 2023))

The above is for Jan through Mar of 2023.

KUMDC

@Paul Newcome Thank you for your quick reply! I think I did what you said but it is still coming up as "#UNPARSEABLE".

=COUNTIFS({Hardee, Highlands Range 1}, @cell = "T2: Training Parents, Teachers, School Personnel, and Community interagency, fairs, etc", {Hardee, Highlands Range 2}, AND(IFERROR(MONTH(@cell), 0)>= 7, IFERROR(MONTH(@cell), 0)<= 9, IFERROR(YEAR(@cell), 0) = 2022)))

Here is a screenshot of what I see, maybe it will help figure out what I am doing wrong?

Paul Newcome

Looks like you have one too many closing parenthesis there on the end.

KUMDC

@Paul Newcombe, you are a genius and my sanity savior! Thank you so much!!! Can you stand one more question? I have 4 other reference pages (counties) I need to pull the same info in from, is there a way to be able to pull it in to the same cell by modifying the formula?

Paul Newcome

Are you wanting to add the results together from all of the sheets? If so, you would create separate COUNTIFS (one for each sheet) and then add them together.

=COUNTIFS(.....) + COUNTIFS(.....)

KUMDC

Absolutely! Thank you! Once again, you are my hero!!! It worked perfectly!!!

Paul Newcome

Happy to help. 👍️

KUMDC

@Paul Newcome

You were an incredible help in April and I am hoping you can help again! I now need to create a new sheet in which I am able to tally how many of each type of deliverables (11 different ones from 5 different sheets) each team member has completed. So the previous formula you helped me with had the same thing but was an accumulation of all team members for a specific deliverable. I do have a column for the team members name on each sheet, but am unsure how to add it.

My previous formula was:

I appreciate any help you can offer!

Thanks

Paul Newcome

@KUMDC It would be another range/criteria set within the COUNTIFS.

KUMDC

@Paul Newcome Once again my hero! Thank you so much!!!

Paul Newcome

Happy to help. 👍️

KUMDC

@Paul Newcome Ugh... I get no errors so I thought it was working, but there is data that it should be counting and it is not so I must have something wrong still. I need it to count if it matches both Range 1 and Range 3 during specified dates-Range 2. Any thoughts or suggestions? Here is how it is currently...

=COUNTIFS({Hardee, Highlands Range 1}, @cell = "S2: Consultation", {Hardee, Highlands Range 3}, @cell = "Rita Ellis", {Hardee, Highlands Range 2}, AND(IFERROR(MONTH(@cell), 0) >= 7, IFERROR(MONTH(@cell), 0) <= 9, IFERROR(YEAR(@cell), 0) = 2023)) + COUNTIFS({Glades, Hendry Range 1}, @cell = "S2: Consultation", {Glades, Hendry Range 3}, @cell = "Rita Ellis", {Glades, Hendry Range 2}, AND(IFERROR(MONTH(@cell), 0) >= 7, IFERROR(MONTH(@cell), 0) <= 9, IFERROR(YEAR(@cell), 0) = 2023)) + COUNTIFS({Martin, Okeechobee, St. Lucie, Indian Rive Range 1}, @cell = "S2: Consultation", {Martin, Okeechobee, St. Lucie, Indian Rive Range 3}, @cell = "Rita Ellis", {Martin, Okeechobee, St. Lucie, Indian Rive Range 2}, AND(IFERROR(MONTH(@cell), 0) >= 7, IFERROR(MONTH(@cell), 0) <= 9, IFERROR(YEAR(@cell), 0) = 2023)) + COUNTIFS({Multi-County Range 1}, @cell = "S2: Consultation", {Multi-County Range 3}, @cell = "Rita Ellis", {Multi-County Range 2}, AND(IFERROR(MONTH(@cell), 0) >= 7, IFERROR(MONTH(@cell), 0) <= 9, IFERROR(YEAR(@cell), 0) = 2023)) + COUNTIFS({Other Counties Range 1}, @cell = "S2: Consultation", {Other Counties Range 3}, @cell = "Rita Ellis", {Other Counties Range 2}, AND(IFERROR(MONTH(@cell), 0) >= 7, IFERROR(MONTH(@cell), 0) <= 9, IFERROR(YEAR(@cell), 0) = 2023))

Thanks!

Paul Newcome

Are you able to provide a screenshot of at least one row it should be counting that it isn't?

KUMDC

Here is a screenshot with the columns I am trying to track highlighted.

Smartsheet Screenshot 2023-08-07 at 8.38.33 AM.png

KUMDC

@Paul Newcome Here is the exact formula I have for the row it should be counting, but isn't.

=COUNTIFS({Hardee, Highlands Range 1}, @cell = "T2: Training Parents, Teachers, School Personnel, and Community interagency, fairs, etc", {Hardee, Highlands Range 3}, @cell = "Cheyney Cushing", {Hardee, Highlands Range 2}, AND(IFERROR(MONTH(@cell), 0) >= 7, IFERROR(MONTH(@cell), 0) <= 9, IFERROR(YEAR(@cell), 0) = 2023)) + COUNTIFS({Glades, Hendry Range 1}, @cell = "T2: Training Parents, Teachers, School Personnel, and Community interagency, fairs, etc", {Glades, Hendry Range 3}, @cell = "Cheyney Cushing", {Glades, Hendry Range 2}, AND(IFERROR(MONTH(@cell), 0) >= 7, IFERROR(MONTH(@cell), 0) <= 9, IFERROR(YEAR(@cell), 0) = 2023)) + COUNTIFS({Martin, Okeechobee, St. Lucie, Indian Rive Range 1}, @cell = "T2: Training Parents, Teachers, School Personnel, and Community interagency, fairs, etc", {Martin, Okeechobee, St. Lucie, Indian Rive Range 3}, @cell = "Cheyney Cushing", {Martin, Okeechobee, St. Lucie, Indian Rive Range 2}, AND(IFERROR(MONTH(@cell), 0) >= 7, IFERROR(MONTH(@cell), 0) <= 9, IFERROR(YEAR(@cell), 0) = 2023)) + COUNTIFS({Multi-County Range 1}, @cell = "T2: Training Parents, Teachers, School Personnel, and Community interagency, fairs, etc", {Multi-County Range 3}, @cell = "Cheyney Cushing", {Multi-County Range 2}, AND(IFERROR(MONTH(@cell), 0) >= 7, IFERROR(MONTH(@cell), 0) <= 9, IFERROR(YEAR(@cell), 0) = 2023)) + COUNTIFS({Other Counties Range 1}, @cell = "T2: Training Parents, Teachers, School Personnel, and Community interagency, fairs, etc", {Other Counties Range 3}, @cell = "Cheyney Cushing", {Other Counties Range 2}, AND(IFERROR(MONTH(@cell), 0) >= 7, IFERROR(MONTH(@cell), 0) <= 9, IFERROR(YEAR(@cell), 0) = 2023))

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.