filterable pivot chart count of "N" from multiple categories

Hello virtualray and Welcome to Excel Forum.
I am not sure that I completely understand the request, however a start may be to produce a filterable chart that sorts from highest to lowest based on the data at the top of the example chart sheet:
1. Use Get & Transform (Power Query) to produce the green/white table on the tbl_ResultCount sheet using the following M-Code (see Power Query advanced editor):

Please Login or Register  to view this content.

2. Produce a pivot table and use more sort options to sort by values in descending order.
3. Produce a chart from the pivot table.
Let us know if you have any questions.

A bit of a Texas Two-Step here.

The problem is that you have to convert the Y's to 1 and the N's to 0.

I solved the issue with a couple of named dynamic ranges.

One of them looks at the first 5 columns on the enter data sheet.Data_List =OFFSET('enter data'!$A$2,0,0,COUNTA('enter data'!$A:$A)-1,5) it gives an array equal to the number of rows for which there is data and 5 columns wide.

On the shadow data sheet the formula in cell A2 is =Data_List. The rest of the columns have formulas copied down to about row 1200. This isn't elegant and I could do a neater job with VBA (and would have for a permanent solution). I overlaid this with another named dynamic range: Pivot_Data =OFFSET('Shadow Data'!$A$1,0,0,COUNTA('Shadow Data'!$A:$A),39). This range only looks at rows for which there is data. It is also the source for the Shadow Chart pivot table.

I did not complete the pivot table on the shadow chart sheet, due to laziness .

I suggest that instead of filters, you use slicers - they just look cooler.




Then there's Pivot_Data =OFFSET('Shadow Data'!$A$1,0,0,COUNTA('Shadow Data'!$A:$A),39)

I found a more elegant solution here.

There is a sheet called Lookups. These are simple pivot tables and they give you a unique list for the various categories. I overlaid these with named dynamic ranges for data validation.

The heavy lifting is done on the charts sheet. Cells B2:B8 have the dropdowns based on the data validations. Column C mimics the selected value. If (All) is selected the value is changed to * for a string value or the minimum for a numeric values. Numeric values are either equal to the selected value (if not all) or greater than or equal to the min value.

Columns B:D, rows 11 to 35 contain the formulas to get the value associated with the item to track. I did items 1 to 14. It is left as an exercise to the student to complete the rest of the formulas.

The remaining formulas are =COUNTIFS(Table1[1-correct inventory?],B$10,Table1[Auditor],Sel_Auditor,Table1[date],Date_Op&Sel_Date,Table1[shift],Sel_Shift,Table1[Mold],Sel_Mold,Table1[Week],Week_Op&Sel_Week,Table1[Month],Sel_Month,Table1[Year],Year_Op&Sel_Year)

Change 1-correct inventory? to the value being tracked. The easiest way to do this is to blank out between the square brackets and type in the number (e.g. 15) and intellisense will suggest the column heading name to use. Press the tab key to accept the value then press enter.

Once you completed cells B25:B35, you can copy / paste the formulas into the other two columns.

Also, you will have to redo the chart once you get all the cells updated.

About the only thing else to mention is that you must refresh the pivot table (any one will do since they are all based on the same data set) after adding or changing data. The lookups will update when the pivot tables update.