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!!!
Best Answer
-
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(.....)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@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?
-
Looks like you have one too many closing parenthesis there on the end.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@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?
-
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(.....)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Absolutely! Thank you! Once again, you are my hero!!! It worked perfectly!!!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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:
=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))
I appreciate any help you can offer!
Thanks
-
@KUMDC It would be another range/criteria set within the COUNTIFS.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome Once again my hero! Thank you so much!!!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@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!
-
Are you able to provide a screenshot of at least one row it should be counting that it isn't?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Here is a screenshot with the columns I am trying to track highlighted.
-
@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))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!