countif, based on slicers and unique id in two arrays

[OTWarrior]

I have two worksheets, one with customer information (Cust_ID, type, location) and one with customer responses (Cust_ID, q1, q2, etc)

I have on a third sheet a summary page, using slicers based on the customer information sheet (so the user can filter down on type or location etc). This part works fine.

on this third sheet, I want to summarise the questions, and count the responses based on the filtered slicers. IE: If the slicers has a filter for UK, then only UK results will show in the summary of the question responses.

The summary has 3 rows for each response type (agree, disagree, N/A) and several columns (for each question).

I can do a simple countif to get the results, but cannot figure out how to get it to filter down based on the slicers. I have tried adding a hidden pivot with the customer ID that changes when you alter the slicer choice, then trying to do an index/match against this, but it doesn't appear to be pulling the correct data (it is pulling 21 records, when I know there are 28 in both the customer list and responses list).

This is the formula I have that works:

Code:

=COUNTIFS('Question Data'!B:B,4)

This is what I have tried that doesn't

Code:

=COUNTIFS('Question Data'!B:B,4,'Question Data'!$A:$A,MATCH('Question Data'!$A:$A,X:X,0))

Any ideas on how to do a count and lookup in a formula?
(I am more used to bringing data together in Access queries, but unfortunately this isn't an option here)

Thank you

 

[Chihiro]

Upload sample workbook that represent your data set with sanitized info.

 

[OTWarrior]

Please see attached

 

[Chihiro]

What version of Excel do you have? That will change the best suited solution significantly.

 

[OTWarrior]

Office 365

 

[Chihiro]

Ok, I'm going into meeting but will update later.

I'd suggest restructuring your Question Data so that you can build relationship in Get & Transform and use slicers and pivottable to present all data (without formula).

 

[OTWarrior]

OK, I have tweaked the base data (not sure if I will be able to long term, but for the purpose of getting the formula correct and working, have done so here).

I have made some progress, in that it can reference when the slicers are filtered, but it isn't showing the count of the number of answers, per question, per selected group. I am not quite sure what it is showing ATM.

I don't know what you mean by get & transform, this isn't a function I am familiar with.

Help!

 

[OTWarrior]

I am wanting this value to change dependent on the filtered slicers. Kind of a formula to do the following:

If unique ID appears in column X, count question 1 result (if result equals 4), repeat for each matched unique ID and exact result. Display in cell total count that matches these criteria.

 

[OTWarrior]

I managed a workaround by adding a hidden table and pulling the values from there.

 

[Chihiro]

Glad you got it solved. Here's method using Get & Transform (PowerQuery).

Both tables converted into Excel Table.
Imported into PowerQuery and Question Data table unpivoted to give flat table structure.
Added custom column with calculation (for disposition given to each Question).

Loaded both tables into Data Model.
Added relationship using Question data's employee as foreign key and Staff data's employee as primary key.

Created pivot tables and slicers and connected to both pivots.