Help with multiple criteria within a date range

Hi 

I previously received help with a formula to count the number of a specific activity 'last month', which was then adjusted to take account of the change into a new year. The formula I am using is:

=COUNTIFS({Product Specialists Activity Range 4}, @cell = "Courtesy Calls - 1 hour", {Product Specialists Activity Range 5}, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY()))))


I now have another similar requirement in another smartsheet but in this one the activity information is in two different columns 'Primary Activity' where an activity example is Break fix, and 'Time taken' where an example is 2 hours.


This is the formula I have tried which is not working:

=COUNTIFS({DRAFT - Engineers Activity Range 1}, @cell = "Break fix", AND{DRAFT - Engineers Activity Range 2}, @cell = "2 hours", AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY())))))


Hope you can help 

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    The "@cell" references still won't cause an error though. I use it every time for consistency and have never had an issue where I needed to remove it. I believe you may have too many closing parenthesis on the end.


    =COUNTIFS({DRAFT - Engineers Activity Range 1}, @cell = "Break fix", {DRAFT - Engineers Activity Range 2}, @cell = "2 hours", {DRAFT - Engineers Activity Range 3},AND(IFERROR(MONTH(@cell),0)=IF(MONTH(TODAY())=1,12,MONTH(TODAY())-1),IFERROR(YEAR(@cell),0)=IF(MONTH(TODAY())=1,YEAR(TODAY())-1,YEAR(TODAY()))))


    Here is a breakdown of what still needs to be closed out by the time we wrap everything up:

    YEAR

    TODAY

    IF

    AND

    COUNTIFS


    You should have 5 closing parenthesis on the end but you have 6.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Happy to help. 👍️


    If you know for sure that everything is closed out in the middle of the formula and that you just need to add some closing parenthesis to the end, you can actually not add any and leave the cell. Smartsheet will automatically add closing parenthesis to the end of a formula to close everything out.

Answers

  • Hayley B
    Hayley B ✭✭✭

    Hi Julia!

    I am not positive on how to fix the above formula, but have you tried using the COLLECT() argument at all?

    You have to use it in conjunction with another argument (can be COUNT, AVG, etc.) but I think that may be easier for what you are trying to accomplish. I have added the reference link below.

    https://help.smartsheet.com/function/collect

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    Try this:

    =COUNTIFS({DRAFT - Engineers Activity Range 1}, "Break fix", {DRAFT - Engineers Activity Range 2}, "2 hours", AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY())))))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You have an extra AND function where it is not needed and you are missing a range.


    =COUNTIFS({DRAFT - Engineers Activity Range 1}, @cell = "Break fix", {DRAFT - Engineers Activity Range 2}, @cell = "2 hours", NEED DATE RANGE HERE, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY())))))

  • Thanks @David Tutwiler and @Paul Newcome for your responses. I have tried both suggestions and neither work. This is what I have tried following Paul's suggestion:

    =COUNTIFS({DRAFT - Engineers Activity Range 1}, @cell = "Break fix", {DRAFT - Engineers Activity Range 2}, @cell = "2 hours", {DRAFT - Engineers Activity Range 3},AND(IFERROR(MONTH(@cell),0)=IF(MONTH(TODAY())=1,12,MONTH(TODAY())-1),IFERROR(YEAR(@cell),0)=IF(MONTH(TODAY())=1,YEAR(TODAY())-1,YEAR(TODAY())))))

    where Range 1 is 'Primary Activity', Range 2 is 'Time taken....' and Range 3 is 'Date of Activity'

    I'm still getting 'unparseable' so I'm guessing I still have something wrong somewhere?

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    You don't need to include @cell = because the COUNTIFS is already assuming that you're passing it a RANGE and then a VALUE to check the range against. I think it should read:

    =COUNTIFS({DRAFT - Engineers Activity Range 1}, "Break fix", {DRAFT - Engineers Activity Range 2}, "2 hours", {DRAFT - Engineers Activity Range 3},AND(IFERROR(MONTH(@cell),0)=IF(MONTH(TODAY())=1,12,MONTH(TODAY())-1),IFERROR(YEAR(@cell),0)=IF(MONTH(TODAY())=1,YEAR(TODAY())-1,YEAR(TODAY())))))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    The "@cell" references still won't cause an error though. I use it every time for consistency and have never had an issue where I needed to remove it. I believe you may have too many closing parenthesis on the end.


    =COUNTIFS({DRAFT - Engineers Activity Range 1}, @cell = "Break fix", {DRAFT - Engineers Activity Range 2}, @cell = "2 hours", {DRAFT - Engineers Activity Range 3},AND(IFERROR(MONTH(@cell),0)=IF(MONTH(TODAY())=1,12,MONTH(TODAY())-1),IFERROR(YEAR(@cell),0)=IF(MONTH(TODAY())=1,YEAR(TODAY())-1,YEAR(TODAY()))))


    Here is a breakdown of what still needs to be closed out by the time we wrap everything up:

    YEAR

    TODAY

    IF

    AND

    COUNTIFS


    You should have 5 closing parenthesis on the end but you have 6.

  • Doh! Thanks, I thought I'd double checked that, obviously need my eyes testing 😄. Thank you for your help.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Happy to help. 👍️


    If you know for sure that everything is closed out in the middle of the formula and that you just need to add some closing parenthesis to the end, you can actually not add any and leave the cell. Smartsheet will automatically add closing parenthesis to the end of a formula to close everything out.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!