Lucy89,
The tricky part here is getting the list of unique Product Codes (as I assume you do not yet have access to the new UNIQUE function).
This solution:
- Assumes your Product Codes are in A2 through A12 (as suggested by the screenshot).
- Requires that cell A15 does not contain a value which may match a Product Code.
- The longer formula with INDEX MATCH and COUNTIF is an array formula so must be entered using Ctrl-Shift-Enter so that Excel automatically generate the curly brackets. The SUMIFS just needs to be entered with the Enter key.
| A
| B
| C
|
1
| Product Codes
| | Parts Used |
2
| SBR 257 | | 4 |
3
| FSD 014 | | 5 |
4
| FSD 014 | | 4 |
5
| SBR 001 | | 1 |
6
| SPR 385 | | 5 |
7
| SBR 257 | | 1 |
8
| | | |
| | | |
| | | |
16
| SBR 257 | | 5 |
17
| FSD 014 | | 9 |
18
| SBR 001 | | 1 |
19
| SPR 385 | | 5 |
20
| | | |
21
| | | |
22
| | | |
23
| | | |
24
| | | |
<colgroup><col><col><col></colgroup><tbody>
</tbody>
Cell A16 contains
=IFERROR(INDEX($A$2:$A$12, MATCH(0, COUNTIF($A$15:A15, $A$2:$A$12&"") + IF($A$2:$A$12="",1,0), 0)), "")
which is entered using CTRL-Shift-Enter to generate
{=IFERROR(INDEX($A$2:$A$12, MATCH(0, COUNTIF($A$15:A15, $A$2:$A$12&"") + IF($A$2:$A$12="",1,0), 0)), "")}
Copy down to A24
Cell C16 contains
=IF(A16="","",SUMIFS($C$2:$C$12,$A$2:$A$12,A16))
Copy down to C24