Excel COUNTIFS not counting data correctly

I am summarizing a questionnaire where, out of 52 "Yes and No" questions (input as Y or N respectively), question 10, 19, 23, 24, 33, 37, 40, 45 that are answered Y need to be counted. I thought COUNTIFS would be my function of choice. This is the function I wrote:

=COUNTIFS(Data!N2:Data!N2,"=Y",Data!W2:Data!W2,"=Y",Data!AA2:Data!AA2,"=Y",Data!AB2:Data!AB2,"=Y",Data!AK2:Data!AK2,"=Y",Data!AO2:Data!AO2,"=Y",Data!AR2:Data!AR2,"=Y",Data!AW2:Data!AW2,"=Y")

I also tried without the duplicated cell:

=COUNTIFS(Data!N2,"=Y",Data!W2,"=Y",Data!AA2,"=Y",Data!AB2,"=Y",Data!AK2,"=Y",Data!AO2,"=Y",Data!AR2,"=Y",Data!AW2,"=Y")

And I tried without the equal sign:

=COUNTIFS(Data!N2,"Y",Data!W2,"Y",Data!AA2,"Y",Data!AB2,"Y",Data!AK2,"Y",Data!AO2,"Y",Data!AR2,"Y",Data!AW2,"Y")

I know items 23, 24, 33, 45 are answered Y, so the function should give me 4, but all of them give me 0.

Help Please!

In advance, thank you!

Linda

give us the sample files so we could help u faster

Originally Posted by jeffreybrown

Hi Linda,

You should be able to just use...

=COUNTIF(N2:AW2,"Y")

...change the range to match your actual range...

The problem with using the range N2:AW2 is that range includes data not specific to my summary. See a further reply below with more detail.

Jeffrey, Tony, and Azumi, Thank you for the suggestions, but let me give you more detail, because that may help find an answer that will work.

I have 500 people answering 52 questions. The questions are set-up in a random order as an attempt to gather a true response and determine their opinion on 6 different Summaries. Some of the questions are bogus and won't be included in the results. Other answers are used in more than one of the 6 Summaries.

The volunteers can either answer Yes or No. Data Entry will enter the Y or N respectively.

My Data: I have the data input on my spreadsheet with the rows representing the volunteer and the columns representing the survey questions.

My Summary: On a second tab, there are 7 columns; A=Volunteer, B=Summary1, C=Summary2, ...G=Summary6. Summary1 is the number of "Yes" answers to Questions 10, 19, 23, 24, 33, 37, 40, 45. Summary2 is the number of "Yes" answers to Questions 2, 6, 7, 8, 13, 35, 39, 42, 44. And so on... through Summary6 is the number of "Yes" answers to Questions 46, 48, 49, 51, 52.

That is why I can't use the "range" suggestion nor the "row" suggestion. I need something that will allow me to specify which cells within the row I want to see a "Y" answer.

Does this give enough background?

STATED ANOTHER WAY...

Is there a way to name non-contiguous cells so they can be treated as a range?

Thank you! L.

Try this

=COUNTIF(Data!N2:Data!N2,"Y")+COUNTIF(Data!W2:Data!W2,"Y")+COUNTIF(Data!AA2:Data!AA2,"Y")+..........and so on

maybe this is what u want?

Azumi

Originally Posted by azumi

Try this

=COUNTIF(Data!N2:Data!N2,"Y")+COUNTIF(Data!W2:Data!W2,"Y")+COUNTIF(Data!AA2:Data!AA2,"Y")+..........and so on

maybe this is what u want?

Azumi

THAT'S IT! Although, even less typing. Since it's "COUNTIF" I don't even need the range. I can use =COUNTIF(Data!N2,"Y")+COUNTIF(Data!W2,"Y")+COUNTIF(Data!AA2,"Y")+..........and so on. THANK YOU, so much!

Originally Posted by jeffreybrown

Hi Linda,

Ok, with your further explanation, if I understand correct, in row 1 you have a header which differentiates what columns should be added together.

=COUNTIFS(A1:G1,"Summary1",A2:G2,"Y")
=COUNTIFS(A1:G1,"Summary2",A2:G2,"Y")
=COUNTIFS(A1:G1,"Volunteer",A2:G2,"Y")

You can even place Summary and Volunteer, the key words in a cell and read off of that cell...

J2 = Summary1
J3 = Summary2
J3 = Volunteer

Now in K2 copied down...

=COUNTIFS($A$1:$G$1,J2,$A$2:$G$2,"Y")

Does this help?

Jeffrey, Thank you for your reply. I think Azumi found the formula I needed. I appreciate your input!

You don't appear to need countif At all....

It's just
=(Data!N2="Y")+(Data!W2="Y")+(Data!AA2="Y")

Thank you, Jonmo1. I'll also give that a try. I didn't consider just adding a non-numeric entry.

Glad for your feedback Linda.....

Azumi