Formula Error with Multiply Criteria from Same Reference Sheet

Janette

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.

Kelly Moore

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

Janette

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.

Kelly Moore

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?