COUNTIFS Error

Linda Gonia

Here is my formula: =COUNTIFS({All Qs}, "Yes", {Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row)

I am getting an #unparseable error.

Each component works using COUNTIF.

Do you see something I don't see.

Thank you,

Linda

Paul Newcome

Are you able to show the formula in the sheet open as if you are about to edit it?

Linda Gonia

Here it is: =COUNTIFS({Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row, {All Qs}, "Yes")

Please note that {All Qs} is a range of 8 columns. {Hospital Match} and {Month} are single columns. Each subcomponent works:

=COUNTIF({All Qs}, "Yes") and =COUNTIF({Hospital Match}, [HFMC Score]$15) and =COUNTIF({Month}, [Primary Column]@row)

I appreciate any help you can provide. I am trying to avoid having to create an COUNTIFS for each question.

Paul Newcome

I don't see any reason right off for the unparseable error. If that is in fact the error you are getting, I will need to see it open in the sheet as if you are about to edit it.

If that is not the error (and even after we get that particular one fixed), you will still have an issue in that you cannot have different range sizes within the same function. Basically, you can either have all ranges a single column or all ranges 8 columns, but not a mix of the two.

Once we get the initial error sorted, you are going to have to write out separate COUNTIFS for each Q column and then add them all together.

=COUNTIFS({Q1}, "Yes", ..............) + COUNTIFS({Q2}, "Yes", ..............)

Linda Gonia

Thank you. I updated the formula for the numerator and the denominator. I tried to put in one expression, see below:

=COUNTIFS({Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row, {Q1-A}, "Yes") + COUNTIFS({Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row, {Q2-A}, "Yes") + COUNTIFS({Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row, {Q3-A}, "Yes") + COUNTIFS({Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row, {Q4-A}, "Yes") + COUNTIFS({Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row, {Q5-A}, "Yes") + COUNTIFS({Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row, {Q6-A}, "Yes") + COUNTIFS({Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row, {Q7-A}, "Yes") + COUNTIFS({Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row, {Q8-A}, "Yes") / COUNTIFS({Q1-A}, OR(@cell = "Yes", @cell = "No"), {Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row) + COUNTIFS({Q2-A}, OR(@cell = "Yes", @cell = "No"), {Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row) + COUNTIFS({Q3-A}, OR(@cell = "Yes", @cell = "No"), {Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row) + COUNTIFS({Q4-A}, OR(@cell = "Yes", @cell = "No"), {Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row) + COUNTIFS({Q5-A}, OR(@cell = "Yes", @cell = "No"), {Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row) + COUNTIFS({Q6-A}, OR(@cell = "Yes", @cell = "No"), {Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row) + COUNTIFS({Q7-A}, OR(@cell = "Yes", @cell = "No"), {Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row) + COUNTIFS({Q8-A}, OR(@cell = "Yes", @cell = "No"), {Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row)

It does not return the correct results. When I put the numerator in one cell, I get 11 which is correct. When I put just the denominator in one cell, I get 16 which is correct. Then I try to put the combined formula in one cell I get 24, which is incorrect. When I tried to add parantheses, I got an unparseable error.

Is there a way to combine the formula, or will I have to calculate separate numerators and denominators.

Thank you.

P.S. I appreciate your prompt comments.

Paul Newcome

You would wrap both the numerator and denominator in their own set of parenthesis so that the addition is done before the division.

=(numerator) / (denominator)

=(COUNTIFS({Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row, {Q1-A}, "Yes") + COUNTIFS({Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row, {Q2-A}, "Yes") + COUNTIFS({Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row, {Q3-A}, "Yes") + COUNTIFS({Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row, {Q4-A}, "Yes") + COUNTIFS({Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row, {Q5-A}, "Yes") + COUNTIFS({Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row, {Q6-A}, "Yes") + COUNTIFS({Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row, {Q7-A}, "Yes") + COUNTIFS({Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row, {Q8-A}, "Yes")) / (COUNTIFS({Q1-A}, OR(@cell = "Yes", @cell = "No"), {Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row) + COUNTIFS({Q2-A}, OR(@cell = "Yes", @cell = "No"), {Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row) + COUNTIFS({Q3-A}, OR(@cell = "Yes", @cell = "No"), {Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row) + COUNTIFS({Q4-A}, OR(@cell = "Yes", @cell = "No"), {Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row) + COUNTIFS({Q5-A}, OR(@cell = "Yes", @cell = "No"), {Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row) + COUNTIFS({Q6-A}, OR(@cell = "Yes", @cell = "No"), {Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row) + COUNTIFS({Q7-A}, OR(@cell = "Yes", @cell = "No"), {Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row) + COUNTIFS({Q8-A}, OR(@cell = "Yes", @cell = "No"), {Hospital Match}, [HFMC Score]$15, {Month}, [Primary Column]@row))