Conditional Counters

[vicstone]

I need to count problems per each client.

Input:
Client Problem
1001 Minor
1001 Critical
1002 Minor
1002 Minor
1005 Critical
1005 Minor
1005 Major
1005 Major
1006 Major
1006 Major
1008 Minor
1008 Critical
1008 Critical

Output that I need:
Client Min Maj Critical
1001 1 0 1
1002 2 0 0
1005 1 2 1
1006 0 2 0
1008 1 0 2

Thank you

[Special-K]

See attached

[TMS]

With Headings Minor, Major and Critical, you can use:

Please Login or Register  to view this content.

But you should probably look at Pivot Tables


Regards, TMS

[vicstone]

Thank you.
However, there is another issue with the client number. I need client number to be populated in the output table as the client numbers changes. I have thousands of clients, and not all of them have problems. In my example Client 1003, 1004 and 1007 were not in the initial table. Hoe can I do this?

[TMS]

Use a Pivot Table.

You can use a formula to extract a list of unique account numbers but you would need to allow for the maximum ... which implies a lot of potentially "blank" cells dependent on the maybe non-existent accounts.

Like I said, look at Pivot Tables.

[vicstone]

Thank you TMS.
I used a Pivot Table and got all counters in place.

[TMS]

You're welcome.

Once you get used to using them, Pivot Tables are wonderful tools. I used to spend a lot of time using Advanced Filters to extract lists and then using COUNTIFS and SUMIFS, etc., to analyse data. Pivot Tables take out all the pain.

Regards, TMS