Nov 19 2020 11:06 AM - edited Nov 19 2020 01:21 PM
I have this Data set on "Sheet 1":
User ID | Call Reason | Product ID | Date Created |
165 | Billing | 1229509 | 1/16/2020 |
165 | Billing | 1644846 | 1/29/2020 |
165 | Billing | 1569706 | 1/30/2020 |
221 | Billing | 1805794 | 2/3/2020 |
221 | CS | N/A | 2/10/2020 |
165 | Product | 1629893 | 2/17/2020 |
221 | CS | 1920137 | 2/19/2020 |
313 | CS | N/A | 2/20/2020 |
165 | CS | N/A | 2/25/2020 |
221 | CS | 1792205 | 2/28/2020 |
165 | Cs | 1590332 | 3/17/2020 |
221 | Billing | 1955170 | 4/3/2020 |
165 | CS | 1747608 | 4/6/2020 |
165 | CS | 1720183 | 8/12/2020 |
165 | CS | N/A | 8/13/2020 |
165 | Billing | 817217 | 8/17/2020 |
165 | Billing | 1667411 | 8/27/2020 |
165 | CS | 1723395 | 9/2/2020 |
165 | Billing | 1707406 | 9/9/2020 |
221 | CS | 1696410 | 9/9/2020 |
165 | Billing | N/A | 9/22/2020 |
165 | CS | 12741998 | 10/1/2020 |
313 | Billing | 1701967 | 10/9/2020 |
165 | Billing | 1686196 | 10/14/2020 |
165 | Billing | 1941560 | 10/19/2020 |
313 | CS | 1699853 | 10/20/2020 |
165 | CS | 1519619 | 10/21/2020 |
165 | CS | 1669708 | 10/29/2020 |
165 | System | N/A | 10/29/2020 |
165 | Tech | 1306081 | 11/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 | Billing | 10 | |
CS | 9 | ||
Product | 1 | ||
Tech | 1 | ||
System | 1 |
Initially I thought VlookUp, but thats not going to tally it and return a result... a little help please? File attached.
Nov 19 2020 12:07 PM
@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.
Nov 19 2020 01:20 PM
@mtarler Its a part of an entire dashboard, so I need to be able to do it without it being a pivot table.
Nov 20 2020 01:40 AM
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 down
PivotTable itself could be removed from the sheet.
Nov 20 2020 08:40 AM
@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.
Nov 20 2020 11:57 AM
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) )