SUM of Unique Values from 2 columns

[thewiseguy]

Hi all. I am using the following formula:

=LET(sh,VSTACK(Input!AD4:AD502,Input!AG4:AG502),SORT(UNIQUE(FILTER(sh,(sh <>"")*(sh<>0),""))))

This is giving me unique values from columns AD and Ah (from "Input" sheet)

Now I am trying to get the total quantities associated with these 2 columns. Column AD has qty in AE and column AG has qty is AH

v2023.10 - Copy.xlsm
	AD	AE	AF	AG	AH
3	Accessory Name	QTY	Product	Accessory Name	QTY
4	MAXLITE External Adapter Round, White	1	Accessory	MAXLITE Battery backup for Type C Linears	3
5	MAXLITE External Adapter Round, White	1	Accessory	MAXLITE Battery backup for Type C Linears	3
Input

Can anyone help with this?

Many thanks in advance!

 

[Fluff]

How about

=SUMIFS(input!AE4:AH502,input!AD4:AG502,A2#)

change the A2# to refer to the cell with your formula.

 

[thewiseguy]

Yours is much more simple. I changed the columns a bit but this was what I used:

=SUMPRODUCT((Input!$Z$7:$Z$506='Accessories (BOM)'!B6)*Input!$AA$7:$AA$506)+SUMPRODUCT((Input!$AC$7:$AC$506='Accessories (BOM)'!B6)*Input!$AD$7:$AD$506)

 

[Fluff]

Glad to help & thanks for the feedback.