Forum Discussion
Create a simple dynamic tally chart via dropdown list WITHOUT a Pivot Table
shade206 so this is one of many things that a pivot table can do for you. I have attached your book updated with a) using a COUNTIFS() formula next to the manual table you created, b) with a pivot table using User ID as a Filter criteria, c) pivot table showing the results with all the User IDs (and a slicer), and d) I also added a "Uniq Users" drop down that uses a dynamically created list of all unique IDs from the data tab that have the character/strings that you start typing in that c6 drop down list. (i.e. you type "3" and then click the drop down it will only show ID 313 since that is the only ID with a "3" in it. I didn't connect anything to this drop down box but included it for show.
mtarler Its a part of an entire dashboard, so I need to be able to do it without it being a pivot table.
shade206 ok, so no pivot table. As I mentioned I did include a formula based solution also in column G (peach colored). If you have array functions you can simplify it using:
=COUNTIFS(Data!$A:$A,$C$3,Data!$B:$B,E3:E7)and only need to enter that and not have to 'fill down'
I also included C6 drop down which is a handy trick to make that drop down filtered based on partial info you enter into the cell. Let's assume you have 300 names you type "da" and it filters the list to only Davids and Daves and Linda etc... instead of 300 names.
In general PivotTable, more exactly cube formulas, also could work. You may create PivotTable adding data to data model, based on it add cube formulas like
Formulas
in H2: =CUBESET("ThisWorkbookDataModel", "[Range].[Call Reason].children", "Call Reason") in H3: =IFNA(CUBERANKEDMEMBER("ThisWorkbookDataModel",$H$2,ROW($A$1:$A$6)),"") in I3: =CUBEVALUE("ThisWorkbookDataModel", "[Measures].[Count of Call Reason]", "[Range].[Call Reason].&[" & $H3 & "]", "[Range].[User ID].&["&$C$3&"]" ) and drag it downPivotTable itself could be removed from the sheet.
