Help! Cannot get COUNTIFS formula to work

KUMDC
KUMDC ✭✭✭
edited 04/06/23 in Formulas and Functions

@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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

«1

Answers

  • Paul Newcome
    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.

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • KUMDC
    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
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • KUMDC
    KUMDC ✭✭✭
    edited 04/07/23

    @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
    Paul Newcome ✭✭✭✭✭✭
    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • KUMDC
    KUMDC ✭✭✭

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • KUMDC
    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:

    =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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • KUMDC
    KUMDC ✭✭✭

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • KUMDC
    KUMDC ✭✭✭
    edited 08/05/23

    @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
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • KUMDC
    KUMDC ✭✭✭

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


  • KUMDC
    KUMDC ✭✭✭
    edited 08/07/23

    @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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!