Help with multiple criteria within a date range

Julia Bailey

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 

Paul Newcome

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

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.

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

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

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())))))

Julia Bailey

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

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

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.

Julia Bailey

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

Paul Newcome

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.