Formula Error with Multiply Criteria from Same Reference Sheet
I have a working formula that counts the number of Injuries per month, per location from a reference sheet, but I now need to add another criteria that will only count those injuries where the worker received first aid. These are identified by the "yes" in the "Onsite First Aid" column. Below is a picture of the original formula that works:
This is how I tried to modify it by including the additional criteria. I get an UNPARSEABLE error message:
Any help would be greatly appreciated.
Answers
-
Hi @Janette
Remove the = [Helper Column]@row from you formula. All you need is "yes" Make sure no more than one space remains before the next comma.
Note that the "yes" in the formula is case sensitive. Are all of your yes answers in the other sheet written as lower case responses? If you have a mixture of upper and lower case responses, you will need to force the response to lower case. If you need this, let me know. You don't n
Kelly
-
Hi Kelly,
Thank you for your response. I need the =[Helper Column]@row part of the formula so only the number for that month is calculated in the cell. I did try your suggestion anyway - being careful about the "yes" and the comma: =COUNTIFS({Location}, CONTAINS(PARENT([Branch Per Month]@row), @cell), {Date of Incident}, ISDATE(@cell), {Date of Incident}, MONTH(@cell), {Onsite First Aid} "Yes", {Date of Incident}, YEAR(@cell) = $[Branch Per Month]$1)
I'm still getting the UNPARSEABLE message.
-
Hi Janette
It appeared to me the formula read "yes"= [Helper Column -Month]@row. The yes wouldn't go with that term - looking now I believe you inadvertently split a term when you added the 'yes' criteria. Double check if you meant this [Helper column] to be part of the Month(@cell) term.
I see my last sentence was truncated as I posted - it was supposed to read no need to manually update yes to lower case, we could do that with a formula if necessary.
Adding the =[helper column-month]@row back to the Month term - did that fix your formula?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!