Hi,
Assume that your data is in range A16:B18, enter SAM in cell A20 and use the
following array fomula (Ctrl+Shift+Enter)
=SUMPRODUCT(1*(FREQUENCY(IF($A$16:$A$18=$A20,IF($B $16:$B$18<"",MATCH("~"&$B$16:$B$18,$B$16:$B$18&"" ,0))),ROW($B$16:$B$18)-ROW($B$15))0))
--
Regards,
Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
"ub" wrote in message
...
Hi
My sheets has 9000 rows and the data is structured as
Column A Coulmn B _____Column C to H
SAM USER1
SAM USER1
SAM USER1
SAM USER2
SAM USER3
PAM USER2
PAM USER2
PAM USER3
PAM USER3
I want to look at Column A, and for each name in coulmn A, I want to count
how many times different users called in for service. The result in the
above
example should be
SAM User1 User2 User3
5 3 1 1
The challange is the names in Column A and Coulmn B are always variable-
no
fixed number of users and no fix number of the representatives
Can someone please advise syntax of the code