I have been struggling to solve this issue for days now.
I have raw data with no specific column structure to it. I want to create a column structure to it so that I can count how many times a specific vehicle was requested by a client. I can accomplish this via hlookup; however, I have too many unique columns (vehicle) and excel runs out of memory upon copying the lookup function. I just need to find the count; so wondering if I can use a countifs function to get the count of clients by vehicle (i.e. table B)
Is there a way I can transform the below table A to table B using 'countifs' function? What will be the B11 formula to get the result=1 (i.e. Honda was only requested once by clientA) in the screenshot?
Table A
Table B:
Here is the snapshot:
I have raw data with no specific column structure to it. I want to create a column structure to it so that I can count how many times a specific vehicle was requested by a client. I can accomplish this via hlookup; however, I have too many unique columns (vehicle) and excel runs out of memory upon copying the lookup function. I just need to find the count; so wondering if I can use a countifs function to get the count of clients by vehicle (i.e. table B)
Is there a way I can transform the below table A to table B using 'countifs' function? What will be the B11 formula to get the result=1 (i.e. Honda was only requested once by clientA) in the screenshot?
Table A
ClientA | Honda | Volvo | Tesla |
ClientB | Mercedes | Tesla | Volvo |
ClientB | Volvo | Honda | Mercedes |
ClientB | Tesla | Volvo | Honda |
ClientA | Volvo | Tesla | Mercedes |
ClientA | Volvo | Mercedes | Tesla |
Table B:
Client | Honda | Mercedes |
ClientA | 1 | 2 |
ClientB | 2 | 2 |
Here is the snapshot: