Counting number of each item assigned to customer with unlabelled columns

Question

I have a table in Excel as follows:

            1  2  3  4  5  6  7 
Customer1   Z  U  A  C  D  A  A
Customer2   B  D  J  S  F  D  B
Customer3   S  A  C  A  A  B  B
Customer4   X  H  A  C  D  A  A
Customer5   B  D  J  S  F  D  B
Customer6   S  A  C  A  A  B  B

What I'm trying to do is get a tally for every customer of how many of each item they have assigned to them. For example, Customer1 has three As and 1 C so we should get:

          A B C
Customer1 3 0 1

And so on.

Is there an easy way to do this that I might be missing?

Answer 1

Set up a table with the customer names as column labels across the top, and row labels with a list of possible characters down. Then use Sumproduct.

Consider the following screenshot. The formula in cell B10 is

=SUMPRODUCT(($B$1:$H$6=$A10)*($A$1:$A$6=B$9))

Answer 2

This can actually be done easily by setting up another table whose columns are table entries and whose rows are the same as the rows in the original table. With this setup =COUNTIF() can be used to count for a given row in the initial table how many times the row header in the new table occurs.