Product Totals

[Lucy89]

Hi please see the photo in the link;

https://ibb.co/RPmLYTz

Is it possible so that A16:A24 will display a product code and C16:C24 will show the total amount of each product?

 

[Toadstool]

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:

1. Assumes your Product Codes are in A2 through A12 (as suggested by the screenshot).
2. Requires that cell A15 does not contain a value which may match a Product Code.
3. 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.

[TABLE="class: outer_border, width: 246"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]Product Codes
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Parts Used[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]SBR 257[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]FSD 014[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]FSD 014[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]SBR 001[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]SPR 385[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"]SBR 257[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]16
[/TD]
[TD="align: center"]SBR 257[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]17
[/TD]
[TD="align: center"]FSD 014[/TD]
[TD="align: center"][/TD]
[TD="align: center"]9[/TD]
[/TR]
[TR]
[TD="align: center"]18
[/TD]
[TD="align: center"]SBR 001[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]19
[/TD]
[TD="align: center"]SPR 385[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]20
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]21
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]22
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]23
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]24
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Lucy89]

Toadstool thank you thank you thank you! This is perfect, fantastic

I like to think I know a little about excel formulas but what’s all this curly brackets thing about please?

 

[Toadstool]

Lucy,

An Array Formula lets you take a function which normally just acts on one cell and have it execute across a range of cells. It's important to note you can't type in those curly brackets as it's the Ctrl-Shift-Enter which tells Excel to treat it as an array formula and Excel puts in the curly brackets to show it has done so.
The Microsoft explanation may help and it can be found here: Array Formulae


If you'd rather avoid array formulae (which can be slow to run) then here's an alternative. Just enter
=IFERROR(INDEX($A$2:$A$12,MATCH(0,INDEX(COUNTIF($A$15:A15,$A$2:$A$12&""),),0)),"")
into A16 and copy down to A24. The SUMIFS statement in column C is unchanged.

	A	B	C
Product Codes	Parts Used
SBR 257
FSD 014
FSD 014
SBR 001
SPR 385
SBR 257
SBR 257
FSD 014
SBR 001
SPR 385

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]20[/TD]

[TD="align: right"][/TD]

</tbody>

ProductCodes​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A16[/TH]
[TD="align: left"]=IFERROR(INDEX($A$2:$A$12,MATCH(0,INDEX(COUNTIF($A$15:A15,$A$2:$A$12&""),),0)),"")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C16[/TH]
[TD="align: left"]=IF(A16="","",SUMIFS($C$2:$C$12,$A$2:$A$12,A16))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Regards,
Toadstool

 

[Lucy89]

Wonderful. I'll take a read. Once again thank you.

 

[Toadstool]

You're welcome!