0

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?

  • Welcome to Super User. Unfortunately, we are not a code-writing service. Instead of simply asking for code to perform a particular task, please show us what you've tried so far (including any code you currently have) and where you're stuck so that we can help you with your specific problem. Questions that only ask for code are too broad and are likely to be put on hold or closed. Please read How do I ask a good question?. – DavidPostill Jul 21 '15 at 20:52
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))

enter image description here

| improve this answer | |
0

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.

| improve this answer | |
  • Can you post the Countif() formula you would use for that? Countif() only has one condition. If anything, Countifs() could be used, but the ranges need to be the same size for each condition, so you would have to add several Countifs() function results together. Or, what exactly did you have in mind? – teylyn Jul 21 '15 at 21:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.