How to summarize outcomes in a spreadsheet-CodePudding

I have a data set (a lot more columns involved than example) however, want to summarize outcomes on a separate sheet.

Well, can’t use pivots as I have to drop data in from an external source prior to the desired outcome.

Staff names will not be sorted also. There are 9 possible outcomes of 1 visit.

DATA EXAMPLE

Staff Name Status Of Visit
Staff 1    Successful 1 
Staff 1    Successful 2 
Staff 1    Unsuccessful 1
Staff 1    Unsuccessful 2
Staff 1    Closed 1
Staff 1    Successful 3
Staff 1    Successful 4
Staff 1    Successful 5
Staff 1    Closed 2
Staff 2    Closed 1
Staff 2    Successful 2
Staff 2    Closed 1
Staff 2    Unsuccessful 1
Staff 3    Successful 3
Staff 3    Successful 1

Desired Output

Staff Name Successful Unsuccessful Closed
Staff 1        5           2          2
Staff 2        1           1          1
Staff 3        2           0          0

and this is the formulas i have used for the staff names

=UNIQUE(staffnames)

and for the counts

=COUNTIFS(staffnames,$D3,B:B,E$2&"*")

but i assume there may be much better way to resolve this, can anyone help

CodePudding user response：

You can try:

Formula in D1:

=LET(X,TRANSPOSE(UNIQUE(TEXTBEFORE(B2:B16," ",-1))),Y,UNIQUE(A2:A16),VSTACK(HSTACK(A1,X),HSTACK(Y,COUNTIFS(A2:A16,Y,B2:B16,X&"*"))))