Excel - help me please

[Selo46]

Hello,

I want to know what formula I need when there is a list of duplicate different manufacturers and a number of duplicate different parts and I want to determine the manufacturer and next to it the number of parts.

Ich möchte wissen welche Formel ich brauche wenn es eine Liste von doppelten verschiedenen Herstellern gibt und eine Anzahl von doppelten verschiedenen Teilen und ich ermitteln möchte das der Hersteller und daneben die Anzahl der Teile auftauchen.

[#2]

I assume that you have somewhere a table along the lines of

Manufacturer Part

It is possible that for optical reasons you chose to lay out

Manufacturer1:
Part1
Part2
Manufacturer2:
Part1
Part2

Please give up on this human layout and switch to Excel's preferred layout:

Manufacturer1 | Part1
Manufacturer1 | Part2
Manufacturer2 | Part1
Manufacturer2 | Part2

It might not be so pleasing to the eye but you can now easily apply Data/Filters. This would be my static lookup, ie a table that tells which parts can be had from which manufacturer. (You can Filter by Part.)

I am now a bit lost. Do you literally just want to know how many parts a manufacturer sells? A really cool way to do this might be to cover the entire first column with a span
=SUBTOTAL(3;COl1). When you then Filter by Manufacturer1, the SUBTOTAL tells you the number of parts. (Filter by Parts and the same tally will tell you how many suppliers there are.)

However, if you additionally had an order table somewhere made up of many rows

ManufacturerX | PartX | QuantityOrdered

and you are wanting to know for each Part in the table above how many had been ordered, then simply add a 3rd (reporting) column to the static database:

| =SUMIFS(Orders/Quantity;Orders/Manufacturers;col1;Orders/Parts;col2)

and that will give you the total orders for each Part.

[#3]

@Selo46 

=SUM(N(IF($A$1:$A$24=D4,MATCH(IF($A$1:$A$24=D4,$B$1:$B$24),IF($A$1:$A$24=D4,$B$1:$B$24),0)=ROW($1:$24))))

If you don't work with Office365 or 2021 you can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

If you work with Office365 or 2021 an easier alternative could be with the UNIQUE function.