Forum Discussion
Conditional XLookups and Counts
I have a spreadsheet with two sheets to count up survey responses. One has all the possible results i.e each survey group (1,2,3,4) where they're based, the question and then possible result. I've tried to come up with a formula that will look up the information and then count the results. i.e if the group in C matches O and the location in E matches P and then question in F matches in row 1 then count the number of matches to the question in Q. I've put in this format to hopefully make things easier, but my real world example has C-I in one sheet and O-Q in another. I've tried COUNTIFS, XLOOKUPS etc but struggling a bit. I appreciate other formats would be better, but this one works for my next steps, I just need this to collate the results as they change. My best attempt at a formula is : =COUNTA(XLOOKUP(C2,O:O,XLOOKUP(E2,P:P,XLOOKUP(F2,N1:Q1,Q2:Q7))))
but this just gives a 1 for everything.
Any excel experts that can help? Or am I never going to manage it!
If I understand your objective, you'll want to use COUNTIFS instead of XLOOKUP.
=COUNTIFS($O$2:$O$7,C2,$P$2:$P$7,E2,$Q$2:$Q$7,F2)
