Using Filter Function how to extract and sum data when different columns have the same name?

[Lacan]

Dear Guys,

It seams so simple that it bothers me.

Accordingly table below using Filter Function how to extract and sum data when different columns have the same name?

Choosing name from data validation in cell B3 how to filtered it with unique name, unique code and unique product and sum the total amount of units across all months?

Hope that you could help.?

Eternally grateful.?

Thank you very much.

Novo Folha de Cálculo do Microsoft Excel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1
2		NAME
3		A
4
5
6		NAME	CODE	PRODUCT	TOTAL SUM UNITS ene.21 until abr.2021								ene. 2021	ene. 2021	ene. 2021	feb. 2021	feb. 2021	feb. 2021	mar. 21	mar. 21	mar. 21	abr. 2021	abr. 2021	abr. 2021
7								NAME	BRICK	CODE	PRODUCT	FACTURED	units	gross	net	units	gross	net	units	gross	net	units	gross	net
8							A	A	315 LX	100	ESPIRO	-	-	-	-	10	50	40	5	23	14	-	-	-
9							B	A	315 LX	200	DICLO	-	2	10	3	-	-	8	4	10	5	9	4	2
10							C	A	315 LX	300	GABA	-	1	5	2	-	-	-	-	-	-	5	2	1
11								C	316 LX	100	ESPIRO	-	5	2	1	-	-	6	3	-	-	2	1	1
12								B	320 LX	50	AMLO	-	-	-	-	10	5	-	-	-	-	7	3	2
13								A	315 LX	40	AMOX	-	2	10	5	-	-	-	-	-	-	6	3	2
14								B	320 LX	10	AVA	-	-	-	-	-	-	5	2	1	1	-	-	-
15								C	316 LX	200	DICO	-	2	5	2	2	1	-	-	-	-	2	1	1
16								C	316 LX	300	GABA	-	-	-	-	-	-	2	1	1	1	1	1	1
17								B	320 LX	50	AMLO	-	2	5	2	1	1	1	1	1	1	4	2	1
18
19
20
Folha10

Cell Formulas
Range	Formula
G8:G10	G8	=SORT(UNIQUE(H8:H17))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B3	List	=$G$8:$G$10

 

[jasonb75]

Works Like Magic.

Be very careful using concatenated criteria, it works with your small example but can often give erroneous results. The correct method is either to check each criteria individually as I have done, or to use a delimiter between each of the criteria and criteria ranges that can not be found in the data. I should also point out that despite making the formula shorter, the concatenating method is less efficient.

I had made a couple of alterations to the sheet on the assumption that the date range to sum could be variable, something that @JEC has not allowed for. If this is not necessary and it will always be the sum of all units in the sheet then my formula can be shortened to

=SUMPRODUCT(($H$8:$H$17=B7)*($J$8:$J$17=C7)*($K$8:$K$17=D7)*($M$7:$X$7="units"),$M$8:$X$17)

and used with the original sheet layout.

 

[Lacan]

Be very careful using concatenated criteria, it works with your small example but can often give erroneous results. The correct method is either to check each criteria individually as I have done, or to use a delimiter between each of the criteria and criteria ranges that can not be found in the data. I should also point out that despite making the formula shorter, the concatenating method is less efficient.

I had made a couple of alterations to the sheet on the assumption that the date range to sum could be variable, something that @JEC has not allowed for. If this is not necessary and it will always be the sum of all units in the sheet then my formula can be shortened to

=SUMPRODUCT(($H$8:$H$17=B7)*($J$8:$J$17=C7)*($K$8:$K$17=D7)*($M$7:$X$7="units"),$M$8:$X$17)

and used with the original sheet layout.

@jasonb75

Sorry.
Thank you very much ?????