Create a simple dynamic tally chart via dropdown list WITHOUT a Pivot Table

Brass Contributor

I have this Data set on "Sheet 1":

User IDCall ReasonProduct IDDate Created
165Billing12295091/16/2020
165Billing16448461/29/2020
165Billing15697061/30/2020
221Billing18057942/3/2020
221CSN/A2/10/2020
165Product16298932/17/2020
221CS19201372/19/2020
313CSN/A2/20/2020
165CSN/A2/25/2020
221CS17922052/28/2020
165Cs15903323/17/2020
221Billing19551704/3/2020
165CS17476084/6/2020
165CS17201838/12/2020
165CSN/A8/13/2020
165Billing8172178/17/2020
165Billing16674118/27/2020
165CS17233959/2/2020
165Billing17074069/9/2020
221CS16964109/9/2020
165BillingN/A9/22/2020
165CS1274199810/1/2020
313Billing170196710/9/2020
165Billing168619610/14/2020
165Billing194156010/19/2020
313CS169985310/20/2020
165CS151961910/21/2020
165CS166970810/29/2020
165SystemN/A10/29/2020
165Tech130608111/16/2020

 

I want to create a chart that returns how many times each user (per the drop down) saw a certain type of call on "Sheet 2" like so:

 

User Call Reason#
165 Billing10
  CS9
  Product1
  Tech1
  System1
 
 

Initially I thought VlookUp, but thats not going to tally it and return a result... a little help please? File attached. 

6 Replies

@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 

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

image.png

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 down

PivotTable itself could be removed from the sheet. 

@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.

@shade206 

Just for the record, the problem as posed can be solved with a single Dynamic Array formula

 

= LET(
  reason, SORT(UNIQUE(Data[Call Reason])),
  count,  COUNTIFS(
     Data[Call Reason], reason,
     Data[User ID], User),
  IF({1,0}, reason, count) )