How to transpose data in an other tab in excel

Question

 am looking for transposing a table into another tab in Excel.

My first table :

First_Name | Last_Name | Company   | Number  | Done (1 = yes / 0 = no)
-----------------------------------------------------
Allison    | Dark      ! McDonald  | 2,00    |  1
John       | Doe       | Amazon    | 10,00   |  1
Julian     | Smith     | Coca Cola | 3,00    |  0
Kathy      | Johnson   | Coca Cola | 20,00   |  1
Barbara    | Brown     | Tesco     | 10,00   |  0
Alexander  | Lee       | Amazon    | 4,00    |  0
Harry      | Moore     | Amazon    | 8,00    |  0

How can I retrieve these data in another tab like this:

Company   | Number of names | Done   |  Sum of Numbers 
------------------------------------------------------
Amazon    |     3           | 1 on 3 |   22,00
Coca Cola |     2           | 1 on 2 |   23,00
McDonald  |     1           | 1 on 1 |   2,00
Tesco     |     1           | 0 on 1 |   10,00

I was looking for a formula but it also can be in vba. Thanks for your help.

Answer 1

If you're using a previous version of Excel, you can copy the entire Company list and use the "Remove duplicates" tool, or you can enter a formula in A13 and drag it down until it's empty:

=IFERROR(INDEX($C$2:$C$8, MATCH(0,COUNTIF($A$12:A12, $C$2:$C$8), 0)),"")

To get the company name count paste in B13 and drag down:

=COUNTIF($C$2:$C$8,A13)

For "Done" in C13:

=COUNTIFS($C$2:$C$8,A13,$E$2:$E$8,1)&" of "&COUNTIF($C$2:$C$8,A13)

For the sum of numbers in D13:

=SUMIF($C$2:$C$8,A13,$D$2:$D$8)

Result: