CountIF depending on other fields? Survey problem

[KapoJones]

First, sorry if that has been asked before, I used the search function but didnt find what I'm looking for.

Hopefully one of you can solve my problem.

I did a survey that goes like this (I simplified things, these are not the real questions):


1. Are you happy?

[yes] [rather yes] [rather no] [no] [not gonna tell you] [I dont know]

2a) If question 1 yes or rather yes, because you ate apple?
2b) If question 1 no or rather no, was it because you didnt get apple?

[yes] [rather yes] [rather no] [no] [not gonna tell you] [I dont know]


So now I have my data collected in a data field, I was thinking its easiest to collect every data point by letters a-f (a = yes, b = rather yes, c= rather no...)

So the data looks like

Question1 Question2
Person1 a b
Person2 a c
Person3 d f
Person4 c a


For making graphs for the 1st question I did
COUNTIF(B:B,"a")
COUNTIF(B:B,"b")
COUNTIF(B:B,"c")

Thats easypeasy. But now I cant get my formula done for the second question depending on the first.

What I want is
Number of A when Question1 is A OR B
Number of B when Question1 is A OR B
Number of C when Question1 is A OR B


And
Number of A when Question1 is C OR D
Number of B when Question1 is C OR D
Number of C when Question1 is C OR D

I really tried hard, but I'm nowhere near a solution, so I'm seeking help here!

 

[gehusi]

{=SUMPRODUCT(IF(OR(B:B="A",B:B="B"),1,0),if(C:C="A",1,0))}

Do not type the { }, after typing the formula use CTRL+SHIFT+ENTER.

Extrapolate for your other examples.

 

[KapoJones]

the formula looks good to me, but it doesnt work.

I only get, #VALUE! - I tried to look into it if its some small error, but couldnt get it working

 

[gehusi]

Restrict the range to only the values you are working with. C1:C100 for example, not C:C.

 

[KapoJones]

Already tried that, but it didnt help.

 

[Blueridge227]

with question 1 in column B and Question 2 in Column C, this should do the trick for you:

Number of A when Question1 is A or B
=COUNTIFS(C:C,"A",B:B,"A")+COUNTIFS(C:C,"A",B:B,"B")

Number of B when Question1 is A or B
=COUNTIFS(C:C,"B",B:B,"A")+COUNTIFS(C:C,"B",B:B,"B")

Number of C when Question1 is A or B
=COUNTIFS(C:C,"C",B:B,"A")+COUNTIFS(C:C,"C",B:B,"B")



Number of A when Question1 is C or D
=COUNTIFS(C:C,"A",B:B,"C")+COUNTIFS(C:C,"A",B:B,"D")

Number of B when Question1 is A or B
=COUNTIFS(C:C,"B",B:B,"C")+COUNTIFS(C:C,"B",B:B,"D")

Number of C when Question1 is A or B
=COUNTIFS(C:C,"C",B:B,"C")+COUNTIFS(C:C,"C",B:B,"D")

 

[KapoJones]

That works!

Thanks so much both gehusi and blueridge227 for helping me.

I was already thinking of skipping conditional questions, but that saves me a bunch of place on my survey

 

[gehusi]

{=SUMPRODUCT(IF(B2:B11="a",1,0),IF(C2:C11="a",1,0))+SUMPRODUCT(IF(B2:B11="b",1,0),IF(C2:C11="a",1,0))}