Multiple Ifs?

[nfs001]

Hello

I would appreciate some help with a spreadsheet I have. I am trying to determine the responses from a survey that is sent out weekly. The respondent reply options are Yes or No, but I would also like to record if they dont respond. I have Sheet 1 where the survey results are pasted, and Sheet 2 where I would like to view the data by report number.

I have tried various If and vlookup formulas but am a novice so not really sure what I'm doing!

Any help would be much appreciated.

Thanks

Incident Reports marie2.xlsx
	A	B	C	D
1	Sheet 1
2	Report	Yes	No	No Response
3	100	Dave
4	100	Jill
5	100		Simon
6	100			Sarah
7	101		Dave
8	101	Simon
9	101			Sarah
10	101		Jill
11	102	Simon
12	102	Jill
13	102	Sarah
14	102			Dave
15
16
17	Sheet 2
18	Name	100	101	102
19	Dave	Yes	No	No
20	Jill	Yes	No	Yes
21	Sarah	No Response	No Response	Yes
22	Simon	No	Yes	Yes
Sheet1

 

[Phuoc]

Try this

Book1
	A	B	C	D
1	Sheet 1
2	Report	Yes	No	No Response
3	100	Dave
4	100	Jill
5	100		Simon
6	100			Sarah
7	101		Dave
8	101	Simon
9	101			Sarah
10	101		Jill
11	102	Simon
12	102	Jill
13	102	Sarah
14	102			Dave
15
16
17	Sheet 2
18	Name	100	101	102
19	Dave	Yes	No	No Response
20	Jill	Yes	No	Yes
21	Sarah	No Response	No Response	Yes
22	Simon	No	Yes	Yes
Sheet1

Cell Formulas
Range	Formula
B19:D22	B19	=TEXTJOIN(" ",1,IF($A$3:$A$14=B$18,IF($B$3:$B$14=$A19,$B$2,"")&IF($C$3:$C$14=$A19,$C$2,"")&IF($D$3:$D$14=$A19,$D$2,""),""))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[etaf]

what version of excel???

how about


=IFERROR(IF(COUNTIFS($B$3:$B$14,$A19,$A$3:$A$14,B$18),"Yes",IF(COUNTIFS($C$3:$C$14,$A19,$A$3:$A$14,B$18),"No",IF(COUNTIFS($D$3:$D$14,$A19,$A$3:$A$14,B$18),"No Response"))),"name/Report not in table")

probaly can do with an index match - But I just thought a countifs() which should work with some older versions of excel

Book1
	A	B	C	D
1	Sheet 1
2	Report	Yes	No	No Response
3	100	Dave
4	100	Jill
5	100		Simon
6	100			Sarah
7	101		Dave
8	101	Simon
9	101			Sarah
10	101		Jill
11	102	Simon
12	102	Jill
13	102	Sarah
14	102			Dave
15
16
17	Sheet 2
18	Name	100	101	102
19	Dave	Yes	No	No Response
20	Jill	Yes	No	Yes
21	Sarah	No Response	No Response	Yes
22	Simon	No	Yes	Yes
Sheet1

Cell Formulas
Range	Formula
B19:D22	B19	=IFERROR(IF(COUNTIFS($B$3:$B$14,$A19,$A$3:$A$14,B$18),"Yes",IF(COUNTIFS($C$3:$C$14,$A19,$A$3:$A$14,B$18),"No",IF(COUNTIFS($D$3:$D$14,$A19,$A$3:$A$14,B$18),"No Response"))),"name/Report not in table")

 

[nfs001]

Excel version Microsoft® Excel® for Microsoft 365 MSO (Version 2301 Build 16.0.16026.20002) 32-bit .

This is brilliant, both answers work.

Thank you so much for taking the time to help me

 

[etaf]

your are welcome - its worth adding the excel version in your profile - so any future questions will know the version as different solutions will apply

 

[nfs001]

Just added it. Thanks again