Sumif table

[himurah182]

Hey guys,
I need your help on this one. I have a table (1st picture) that is going to be filled with the quantities of each product that were sold to each customer.
However, to be more specific, I need a second table(2nd picture) that calculates the total cost that each customer had in each type of product.
My goal with this excel file is to fill column "D" with data, next I would register and once it was done, the sum of each customers goods that were bought would appear in the second table.
I have tried to do a sumif, but it did not work properly since the function moved everytime a new column of data was registed.
Picture 3 has the unit prices of each product.
What should I do?

 

[SanjayGMusafir]

You can also try this, if you want to keep formula and outlook simple.

All Records.xlsb
	O	P	Q	R	S	T	U	V
1	Where code	BP MAIO 2022	type 1	type 2	type 3	type 4	type 5
2		Postos/vendas	Clipper	Relogios	Borda d'Água	Restantes Artigos	Tubos	Total
3	26253	Cacem-Av B Amigos	0.45	0.00	0.00	0.00	0.00	0.45
4	26507	Santarem/ nascente	0.00	0.00	0.00	0.00	0.00	0.00
5	26800	Santarem/poente	0.00	0.00	0.00	0.00	0.00	0.00
6	27036	Almada V.R.-Norte	0.00	0.00	0.00	0.00	0.00	0.00
7	27037	Almada V.R.-Sul	32.48	68.75	88.95	8.00	10.10	208.28
8	27004	Pêro Pinheiro	0.00	0.00	0.00	0.00	0.00	0.00
9	27008	Ic 19-Poente	0.00	0.00	0.00	0.00	0.00	0.00
10	27078	Ramalhão	32.48	68.75	88.95	8.00	10.10	208.28
11	27114	Mealhada-Nascente	0.00	0.00	0.00	0.00	0.00	0.00
12	27115	Mealhada-Poente	0.00	0.00	0.00	0.00	0.00	0.00
13	27168	Santarem -Variante	0.00	0.00	0.00	0.00	0.00	0.00
14	27406	Duarte Pacheco	0.00	0.00	0.00	0.00	0.00	0.00
15	27311	A21-Mafra Nasc/N	0.00	0.00	0.00	0.00	0.00	0.00
16	27312	A21-Mafra Poen/S	0.00	0.00	0.00	0.00	0.00	0.00
17	27088	Aeroporto	0.00	0.00	0.00	0.00	0.00	0.00
18	27020	Albufeira V. R.	0.00	0.00	0.00	0.00	0.00	0.00
19	27010	Amadora	0.00	0.00	0.00	0.00	0.00	0.00
20	27011	Amadora II-Funch.	0.00	0.00	0.00	0.00	0.00	0.00
21	27006	Areeiro	0.00	0.00	0.00	0.00	0.00	0.00
22	27145	Braga-31 Janeiro	0.00	0.00	0.00	0.00	0.00	0.00
Sheet2

Cell Formulas
Range	Formula
Q3:U22	Q3	=SUMPRODUCT($D$8:$D$78*$F$8:$H$78*($A$8:$A$78=Q$1)*($F$6:$H$6=$O3))
V3:V22	V3	=SUM(Q3:U3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Q:V	Cell Value	=0	text	NO

 

[himurah182]

Hi there
Thank you for the formula but it appears to not be working.
What did I do wrong?
Sheet1= 1st picture
BP MAIO 2022 = 3rd picture

calc3 (2).xlsm
	A	B	C	D	E	F
1
2
3		E84/(1+$E$82)
4
5			Fatura
6			Where code		26253	26253
7		Plu	Produto	Preço Und	Cacem-Av B Amigos	Cacem-Av B Amigos
8	type 1	103581	RPBLACK2-BLACK ULTRA THIN 1,1/4	0,44293	98	82
9	type 1	103582	RPBLACK3-BLACK KING SIZE	0,48293	50	7
10	type 1	103583	BOLSA FILTROS REGULAR-8MM-30B-100F	0,47024	67	70
11	type 1	103584	BOLSA FILTROS SLIM-6MM-34B-120F	0,42244	35	46
12	type 1	103585	ISQUEIRO CLIPPER PEQ. CP22RH	0,59220	23	19
13	type 1	103586	ISQUEIRO CLIPPER PEQ FUN	1,72683	64	45
14	type 1	103587	ISQUERIRO CLIPPER GRD CP11RH	0,67805	49	32
15	type 1	103902	ISQUEIRO CLIPPER CLASS.METAL LARG CAIXA	6,58537	42	63
16	type 1	103903	BOLSA FILTROS LONG SLIM-6MM-30B-100F	0,54341	92	64
17	type 1	103904	BOLSA FILTROS LONG REGULAR-8MM-20B-100F	0,54341	8	89
18	type 1	105457	ISQUEIRO Clipper Classic POP CPR11H	1,71220	91	8
19	type 1	106629	ISQUEIRO MINI CLIPPER TUBE	0,89756	68	45
20	type 1	106630	PAPEL CLIPPER BLUE ULTRA THIN REGULAR	0,35122	9	49
21	type 1	106631	PAPEL CLIPPER PURE ULTRA THIN REGULAR	0,35122	88	63
22	type 1	106632	PAPEL CLIPPERPURE ULTRA THIN KSS	0,53171	28	100
23	type 1	106633	PAPEL CLIPPER RED REGULAR CLASSIC	0,32976	21	37
24	type 1	106634	PAPEL CLIPPER GREEN CUT CORNERS REGULAR	0,32976	41	28
25	type 1	106636	MÁQUINA ENROLAR	1,65561	31	72
26	type 1	106637	FILTROS POP UP EXTRA SLIM	0,66829	8	40
27	type 1	106638	MEN-FILTROS POP UP EXTRA SLIM MENTOL	0,74439	8	52
28	type 1	106639	FILTROS SLIM ORGANIC BOLSA	0,63220	22	66
29	type 1	107670	SET KING SIZE SLIM+FILTROS DE PAPEL	0,83902	51	61
30	type 1	116773	PAPEL RAW ORG HEMP KING SIZE SLIM	0,56098	37	58
31	type 1	116775	PAPEL RAW CLASSIC KING SIZE SLIM	0,53171	39	70
32	type 1	116774	PAPEL RAW ORG REGULAR	0,37561	68	7
33	type 1	116776	FILTROS RAW ORGANIC SLIM	0,63415	41	70
34	Type 2	103588	RELÓGIO Medium Price	16,78049	19	45
35	Type 2	103589	RELÓGIO Low Price	13,41463	83	7
36	Type 2	103905	RELOGIO ANALOGICO	26,87805	31	43
37	Type 2	106640	SMARTWATCH SAMI	44,77073	76	33
38	Type 3	106506	CIGARREIRA CAIXA PLASTICO	2,58537	55	67
39	Type 3	106506	CIGARREIRA SILICONE	2,58537	40	67
40	Type 3	106661	Cigarreira Cartão	2,58537	90	57
41	Type 3	106506	CIGARREIRA CAIXA METAL	2,58537	24	17
42	Type 3	103906	LINHA PORTUGAL PORTA MOEDAS	3,30732	38	81
43	Type 3	103907	LINHA PORTUGAL ESTOJO	4,38049	100	64
44	Type 3	103908	LINHA PORTUGAL CARTEIRA 2	5,55122	10	64
45	Type 3	103909	LINHA PORTUGAL CARTEIRA	5,55122	40	26
46	Type 3	103910	CARTEIRA PRIMAVERA C/FECHO 119 BAND	5,51220	33	11
47	Type 3	103911	CARTEIRA PRIMAVERA GRANDE 110	4,38049	4	16
48	Type 3	103912	CARTEIRA PRIMAVERA PEQUEÑA 111	3,95122	49	53
49	Type 3	103913	MATRAFONA GIGANTE	11,17073	73	73
50	Type 3	103914	BLOCO NOTAS	1,57073	83	43
51	Type 3	103915	PULSERIRA PELE COM CAIXA	12,28293	95	55
52	Type 3	103916	LICOREIRA CORTIÇA	6,68293	24	19
53	Type 3	103917	PULSEIRAS CORTIÇA	6,68293	92	76
54	Type 3	103918	COLARES CORTIÇA	7,79512	87	95
55	Type 3	103919	CANETA CORTIÇA COM CAIXA	5,55122	25	98
56	Type 3	106507	PELUCHE TAM 1	10,03902	52	8
57	Type 3	106508	PELUCHE TAM 3	15,65854	69	66
58	Type 3	106509	OCULOS SOL	15,30732	53	13
59	Type 3	106641	CARTAS JOGAR PLÁSTICO	5,07317	77	62
60	Type 3	106642	CARTEIRA GB SRA PELE	24,63415	49	74
61	Type 3	106643	CARTEIRA GB HM PELE-PEQ	14,52683	34	65
62	Type 3	106644	CARTEIRA GB HM PELE- GR	16,78049	74	43
63	Type 3	106645	SACA ROLHAS CORTIÇA	2,78049	94	5
64	Type 3	106654	CIGARREIRAS CORTIÇA	5,60976	11	10
65	Type 3	106655	MINIATURA V. PORTO/LICOR BEIRÃO	3,36585	66	51
66	Type 3	106656	CONJ 2 MINIATURAS PORTO+BEIRÃO	5,07317	60	67
67	Type 3	106657	PORTA MOEDAS PEQ CORTIÇA-VÁRIOS MODELOS	4,43902	15	50
68	Type 3	106658	PORTA MOEDAS DA AVÓ-CORTIÇA	7,70732	71	56
69	Type 3	106659	PORTA MOEDAS HM -CORTIÇA	8,92683	74	79
70	Type 3	106660	PORTA MOEDAS MOCHILA CORTIÇA	5,90244	73	45
71	Type 3	107671	PUZZLES- SORTIDOS	3,36585	26	78
72	Type 3	107672	JOGO TABULEIRO-SORTIDO	6,14634	44	4
73	Type 3	107673	OVO DINOSSAURO	3,08293	22	28
74	Type 3	107674	CONJ CORTIÇA	11,17073	77	61
75	type 4	103804	BORDA D'ÁGUA	1,56098	31	54
76	type 5	114755	TUBOS T&T 500 UND	1,97073	89	96
77	type 5	115903	TUBOS KORONA 300 UND	1,09463	29	41
78	type 5	115902	TUBOS KORONA 500 UND	1,97073	90	78
Sheet1

Cell Formulas
Range	Formula
E6	E6	=IFNA(INDEX(Sheet2!B3:B215,MATCH(Sheet1!E7,Sheet2!C3:C215,0)),"")
D8:D78	D8	=E84/(1+$E$82)
E8:E78	E8	=RANDBETWEEN(1,100)

Cells with Data Validation
Cell	Allow	Criteria
E7:F7	List	=Sheet2!$D$3#

calc3 (2).xlsm
	A	B	C	D	E	F	G
1	Where code	BP MAIO 2022	type 1	type 2	type 3	type 4	type 5
2		Postos/vendas	Clipper	Relogios	Borda d'Água	Restantes Artigos	Tubos
3	26253	Cacem-Av B Amigos
4	26507	Santarem/ nascente
5	26800	Santarem/poente
6	27036	Almada V.R.-Norte
7	27037	Almada V.R.-Sul
8	27004	Pêro Pinheiro
9	27008	Ic 19-Poente
10	27078	Ramalhão
11	27114	Mealhada-Nascente
12	27115	Mealhada-Poente
BP MAIO 2022

Cell Formulas
Range	Formula
C3:G12	C3	=IF(SUMPRODUCT(Sheet1!$D$8:$D$78*Sheet1!$G$8:$H$78*(Sheet1!$A$8:$A$78='BP MAIO 2022'!Q$1)*(Sheet1!$G$6:$H$6='BP MAIO 2022'!$O3))=0,"",SUMPRODUCT(Sheet1!$D$8:$D$78*Sheet1!$G$8:$H$78*(Sheet1!$A$8:$A$78='BP MAIO 2022'!Q$1)*(Sheet1!$G$6:$H$6='BP MAIO 2022'!$O3)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C3:G3,F4:G217,C4:E215	Cell Value	<0	text	NO

 

[SanjayGMusafir]

See there is a particular challenge with SUMPRODUCT Function -
If you are making multiple conditions, each condition needs to be properly bracketed separately.

Also when you write/use Long Sheet name like yours BP MAIO 2022 it is advised to use _ as a connector to avoid any error while Referring to a sheet - So change sheet name to BP_MAIO_2022 in case you need that long - Using spaces in Sheet name is invitation to errors.

You wrote -

=SUMPRODUCT(Sheet1!$D$8:$D$78*Sheet1!$G$8:$H$78*(Sheet1!$A$8:$A$78='BP MAIO 2022'!Q$1)*(Sheet1!$G$6:$H$6='BP MAIO 2022'!$O3)

But it needs to be -

=SUMPRODUCT((Sheet1!$D$8:$D$78)*(Sheet1!$G$8:$H$78)*(Sheet1!$A$8:$A$78='BP_MAIO_2022'!Q$1)*(Sheet1!$G$6:$H$6='BP_MAIO_2022'!$O3))

First try the above then further add IF conditions to it.
Also, remember to change Sheet name in above formula in case you change it.

Try it and tell

Best wishes

 

[SanjayGMusafir]

See there is a particular challenge with SUMPRODUCT Function -
If you are making multiple conditions, each condition needs to be properly bracketed separately.

Also when you write/use Long Sheet name like yours BP MAIO 2022 it is advised to use _ as a connector to avoid any error while Referring to a sheet - So change sheet name to BP_MAIO_2022 in case you need that long - Using spaces in Sheet name is invitation to errors.

You wrote -

=SUMPRODUCT(Sheet1!$D$8:$D$78*Sheet1!$G$8:$H$78*(Sheet1!$A$8:$A$78='BP MAIO 2022'!Q$1)*(Sheet1!$G$6:$H$6='BP MAIO 2022'!$O3)

But it needs to be -

=SUMPRODUCT((Sheet1!$D$8:$D$78)*(Sheet1!$G$8:$H$78)*(Sheet1!$A$8:$A$78='BP_MAIO_2022'!Q$1)*(Sheet1!$G$6:$H$6='BP_MAIO_2022'!$O3))

First try the above then further add IF conditions to it.
Also, remember to change Sheet name in above formula in case you change it.

Try it and tell

Best wishes

In fact I just noticed you are working on sheet BP MAIO 2022 only so actual formula should be

=SUMPRODUCT((Sheet1!$D$8:$D$78)*(Sheet1!$G$8:$H$78)*(Sheet1!$A$8:$A$78=Q$1)*(Sheet1!$G$6:$H$6=$O3))

 

[SanjayGMusafir]

In fact I just noticed you are working on sheet BP MAIO 2022 only so actual formula should be

=SUMPRODUCT((Sheet1!$D$8:$D$78)*(Sheet1!$G$8:$H$78)*(Sheet1!$A$8:$A$78=Q$1)*(Sheet1!$G$6:$H$6=$O3))

I just checked cell references are also wrong -

=SUMPRODUCT((Sheet1!$D$8:$D$78)*(Sheet1!$G$8:$H$78)*(Sheet1!$A$8:$A$78=Q$1)*(Sheet1!$G$6:$H$6=$O3))

(Sheet1!$A$8:$A$78=Q$1) - It should be from (Sheet1!$A$8:$A$78=C$1)

(Sheet1!$G$6:$H$6=$O3) - It should be (Sheet1!$F$6:$H$6=$A3)

Try this now -

=SUMPRODUCT((Sheet1!$D$8:$D$78)*(Sheet1!$G$8:$H$78)*(Sheet1!$A$8:$A$78=C$1)*(Sheet1!$F$6:$H$6=$C3))

 

[himurah182]

Hey there
It is still not working.
I don't know if it helps but my macro inserts a new column each time I paste the data. This might influence the formula.

calc3 (2).xlsm
	A	B	C	D	E	F	G	H
1
2
3
4
5			Fatura
6			Where code		27037	27037	27037	26253
7		Plu	Produto	Preço Und	Almada V.R.-Sul	Almada V.R.-Sul	Almada V.R.-Sul	Cacem-Av B Amigos
8	type 1	103581	RPBLACK2-BLACK ULTRA THIN 1,1/4	0,44293	30	42		1
9	type 1	103582	RPBLACK3-BLACK KING SIZE	0,48293	12	81	4
10	type 1	103583	BOLSA FILTROS REGULAR-8MM-30B-100F	0,47024	36	13
11	type 1	103584	BOLSA FILTROS SLIM-6MM-34B-120F	0,42244	84	21
12	type 1	103585	ISQUEIRO CLIPPER PEQ. CP22RH	0,59220	74	93
13	type 1	103586	ISQUEIRO CLIPPER PEQ FUN	1,72683	21	78
14	type 1	103587	ISQUERIRO CLIPPER GRD CP11RH	0,67805	32	24
15	type 1	103902	ISQUEIRO CLIPPER CLASS.METAL LARG CAIXA	6,58537	70	60
16	type 1	103903	BOLSA FILTROS LONG SLIM-6MM-30B-100F	0,54341	84	16
17	type 1	103904	BOLSA FILTROS LONG REGULAR-8MM-20B-100F	0,54341	12	71
18	type 1	105457	ISQUEIRO Clipper Classic POP CPR11H	1,71220	86	44
19	type 1	106629	ISQUEIRO MINI CLIPPER TUBE	0,89756	11	91
20	type 1	106630	PAPEL CLIPPER BLUE ULTRA THIN REGULAR	0,35122	99	13
21	type 1	106631	PAPEL CLIPPER PURE ULTRA THIN REGULAR	0,35122	67	95
22	type 1	106632	PAPEL CLIPPERPURE ULTRA THIN KSS	0,53171	58	8
23	type 1	106633	PAPEL CLIPPER RED REGULAR CLASSIC	0,32976	53	36
24	type 1	106634	PAPEL CLIPPER GREEN CUT CORNERS REGULAR	0,32976	35	44
25	type 1	106636	MÁQUINA ENROLAR	1,65561	2	68
26	type 1	106637	FILTROS POP UP EXTRA SLIM	0,66829	61	47
27	type 1	106638	MEN-FILTROS POP UP EXTRA SLIM MENTOL	0,74439	29	52
28	type 1	106639	FILTROS SLIM ORGANIC BOLSA	0,63220	56	33
29	type 1	107670	SET KING SIZE SLIM+FILTROS DE PAPEL	0,83902	77	3
30	type 1	116773	PAPEL RAW ORG HEMP KING SIZE SLIM	0,56098	30	40
31	type 1	116775	PAPEL RAW CLASSIC KING SIZE SLIM	0,53171	36	88
32	type 1	116774	PAPEL RAW ORG REGULAR	0,37561	87	66
33	type 1	116776	FILTROS RAW ORGANIC SLIM	0,63415	87	59
34	Type 2	103588	RELÓGIO Medium Price	16,78049	2	35
35	Type 2	103589	RELÓGIO Low Price	13,41463	35	92
36	Type 2	103905	RELOGIO ANALOGICO	26,87805	2	24
37	Type 2	106640	SMARTWATCH SAMI	44,77073	26	17
38	Type 3	106506	CIGARREIRA CAIXA PLASTICO	2,58537	55	11
39	Type 3	106506	CIGARREIRA SILICONE	2,58537	26	2
40	Type 3	106661	Cigarreira Cartão	2,58537	1	92
41	Type 3	106506	CIGARREIRA CAIXA METAL	2,58537	73	46
42	Type 3	103906	LINHA PORTUGAL PORTA MOEDAS	3,30732	72	3
43	Type 3	103907	LINHA PORTUGAL ESTOJO	4,38049	73	73
44	Type 3	103908	LINHA PORTUGAL CARTEIRA 2	5,55122	54	42
45	Type 3	103909	LINHA PORTUGAL CARTEIRA	5,55122	2	82
46	Type 3	103910	CARTEIRA PRIMAVERA C/FECHO 119 BAND	5,51220	70	25
47	Type 3	103911	CARTEIRA PRIMAVERA GRANDE 110	4,38049	98	38
48	Type 3	103912	CARTEIRA PRIMAVERA PEQUEÑA 111	3,95122	48	14
49	Type 3	103913	MATRAFONA GIGANTE	11,17073	54	98
50	Type 3	103914	BLOCO NOTAS	1,57073	60	49
51	Type 3	103915	PULSERIRA PELE COM CAIXA	12,28293	64	63
52	Type 3	103916	LICOREIRA CORTIÇA	6,68293	62	47
53	Type 3	103917	PULSEIRAS CORTIÇA	6,68293	4	38
54	Type 3	103918	COLARES CORTIÇA	7,79512	81	35
55	Type 3	103919	CANETA CORTIÇA COM CAIXA	5,55122	51	99
56	Type 3	106507	PELUCHE TAM 1	10,03902	83	18
57	Type 3	106508	PELUCHE TAM 3	15,65854	62	68
58	Type 3	106509	OCULOS SOL	15,30732	91	79
59	Type 3	106641	CARTAS JOGAR PLÁSTICO	5,07317	51	79
60	Type 3	106642	CARTEIRA GB SRA PELE	24,63415	76	77
61	Type 3	106643	CARTEIRA GB HM PELE-PEQ	14,52683	91	7
62	Type 3	106644	CARTEIRA GB HM PELE- GR	16,78049	82	10
63	Type 3	106645	SACA ROLHAS CORTIÇA	2,78049	85	45
64	Type 3	106654	CIGARREIRAS CORTIÇA	5,60976	29	42
65	Type 3	106655	MINIATURA V. PORTO/LICOR BEIRÃO	3,36585	29	2
66	Type 3	106656	CONJ 2 MINIATURAS PORTO+BEIRÃO	5,07317	53	74
67	Type 3	106657	PORTA MOEDAS PEQ CORTIÇA-VÁRIOS MODELOS	4,43902	45	33
68	Type 3	106658	PORTA MOEDAS DA AVÓ-CORTIÇA	7,70732	8	64
69	Type 3	106659	PORTA MOEDAS HM -CORTIÇA	8,92683	11	17
70	Type 3	106660	PORTA MOEDAS MOCHILA CORTIÇA	5,90244	7	47
71	Type 3	107671	PUZZLES- SORTIDOS	3,36585	99	49
72	Type 3	107672	JOGO TABULEIRO-SORTIDO	6,14634	46	9
73	Type 3	107673	OVO DINOSSAURO	3,08293	49	22
74	Type 3	107674	CONJ CORTIÇA	11,17073	88	13
75	type 4	103804	BORDA D'ÁGUA	1,56098	48	29
76	type 5	114755	TUBOS T&T 500 UND	1,97073	41	19
77	type 5	115903	TUBOS KORONA 300 UND	1,09463	33	21
78	type 5	115902	TUBOS KORONA 500 UND	1,97073	56	84
79
80
81
82				Desconto	2,5%
83					unit price
84	type 1	103581	RPBLACK2-BLACK ULTRA THIN 1,1/4		0,454
85	type 1	103582	RPBLACK3-BLACK KING SIZE		0,495
86	type 1	103583	BOLSA FILTROS REGULAR-8MM-30B-100F		0,482
87	type 1	103584	BOLSA FILTROS SLIM-6MM-34B-120F		0,433
88	type 1	103585	ISQUEIRO CLIPPER PEQ. CP22RH		0,607
89	type 1	103586	ISQUEIRO CLIPPER PEQ FUN		1,77
90	type 1	103587	ISQUERIRO CLIPPER GRD CP11RH		0,695
91	type 1	103902	ISQUEIRO CLIPPER CLASS.METAL LARG CAIXA		6,75
92	type 1	103903	BOLSA FILTROS LONG SLIM-6MM-30B-100F		0,557
93	type 1	103904	BOLSA FILTROS LONG REGULAR-8MM-20B-100F		0,557
94	type 1	105457	ISQUEIRO Clipper Classic POP CPR11H		1,755
95	type 1	106629	ISQUEIRO MINI CLIPPER TUBE		0,92
96	type 1	106630	PAPEL CLIPPER BLUE ULTRA THIN REGULAR		0,36
97	type 1	106631	PAPEL CLIPPER PURE ULTRA THIN REGULAR		0,36
98	type 1	106632	PAPEL CLIPPERPURE ULTRA THIN KSS		0,545
99	type 1	106633	PAPEL CLIPPER RED REGULAR CLASSIC		0,338
100	type 1	106634	PAPEL CLIPPER GREEN CUT CORNERS REGULAR		0,338
101	type 1	106636	MÁQUINA ENROLAR		1,697
102	type 1	106637	FILTROS POP UP EXTRA SLIM		0,685
103	type 1	106638	MEN-FILTROS POP UP EXTRA SLIM MENTOL		0,763
104	type 1	106639	FILTROS SLIM ORGANIC BOLSA		0,648
105	type 1	107670	SET KING SIZE SLIM+FILTROS DE PAPEL		0,86
106	type 1	116773	PAPEL RAW ORG HEMP KING SIZE SLIM		0,575
107	type 1	116775	PAPEL RAW CLASSIC KING SIZE SLIM		0,545
108	type 1	116774	PAPEL RAW ORG REGULAR		0,385
109	type 1	116776	FILTROS RAW ORGANIC SLIM		0,65
110	type 2	103588	RELÓGIO Medium Price		17,2
111	type 2	103589	RELÓGIO Low Price		13,75
112	type 2	103905	RELOGIO ANALOGICO		27,55
113	type 2	106640	SMARTWATCH SAMI		45,89
114	type 3	106506	CIGARREIRA CAIXA PLASTICO		2,65
115	type 3	106506	CIGARREIRA SILICONE		2,65
116	type 3	106661	Cigarreira Cartão		2,65
117	type 3	106506	CIGARREIRA CAIXA METAL		2,65
118	type 3	103906	LINHA PORTUGAL PORTA MOEDAS		3,39
119	type 3	103907	LINHA PORTUGAL ESTOJO		4,49
120	type 3	103908	LINHA PORTUGAL CARTEIRA 2		5,69
121	type 3	103909	LINHA PORTUGAL CARTEIRA		5,69
122	type 3	103910	CARTEIRA PRIMAVERA C/FECHO 119 BAND		5,65
123	type 3	103911	CARTEIRA PRIMAVERA GRANDE 110		4,49
124	type 3	103912	CARTEIRA PRIMAVERA PEQUEÑA 111		4,05
125	type 3	103913	MATRAFONA GIGANTE		11,45
126	type 3	103914	BLOCO NOTAS		1,61
127	type 3	103915	PULSERIRA PELE COM CAIXA		12,59
128	type 3	103916	LICOREIRA CORTIÇA		6,85
129	type 3	103917	PULSEIRAS CORTIÇA		6,85
130	type 3	103918	COLARES CORTIÇA		7,99
131	type 3	103919	CANETA CORTIÇA COM CAIXA		5,69
132	type 3	106507	PELUCHE TAM 1		10,29
133	type 3	106508	PELUCHE TAM 3		16,05
134	type 3	106509	OCULOS SOL		15,69
135	type 3	106641	CARTAS JOGAR PLÁSTICO		5,2
136	type 3	106642	CARTEIRA GB SRA PELE		25,25
137	type 3	106643	CARTEIRA GB HM PELE-PEQ		14,89
138	type 3	106644	CARTEIRA GB HM PELE- GR		17,2
139	type 3	106645	SACA ROLHAS CORTIÇA		2,85
140	type 3	106654	CIGARREIRAS CORTIÇA		5,75
141	type 3	106655	MINIATURA V. PORTO/LICOR BEIRÃO		3,45
142	type 3	106656	CONJ 2 MINIATURAS PORTO+BEIRÃO		5,2
143	type 3	106657	PORTA MOEDAS PEQ CORTIÇA-VÁRIOS MODELOS		4,55
144	type 3	106658	PORTA MOEDAS DA AVÓ-CORTIÇA		7,9
145	type 3	106659	PORTA MOEDAS HM -CORTIÇA		9,15
146	type 3	106660	PORTA MOEDAS MOCHILA CORTIÇA		6,05
147	type 3	107671	PUZZLES- SORTIDOS		3,45
148	type 3	107672	JOGO TABULEIRO-SORTIDO		6,3
149	type 3	107673	OVO DINOSSAURO		3,16
150	type 3	107674	CONJ CORTIÇA		11,45
151	type 4	103804	BORDA D'ÁGUA		1,6
152	type 5	114755	TUBOS T&T 500 UND		2,02
153	type 5	115903	TUBOS KORONA 300 UND		1,122
154	type 5	115902	TUBOS KORONA 500 UND		2,02
Sheet1

Cell Formulas
Range	Formula
E6	E6	=IFNA(INDEX(Sheet2!B3:B215,MATCH(Sheet1!E7,Sheet2!C3:C215,0)),"")
D8:D78	D8	=E84/(1+$E$82)
E8:E78	E8	=RANDBETWEEN(1,100)

Cells with Data Validation
Cell	Allow	Criteria
E7:H7	List	=Sheet2!$D$3#

calc3 (2).xlsm
	A	B	C	D	E	F	G
1	Where code	BP MAIO 2022	type 1	type 2	type 3	type 4	type 5
2		Postos/vendas	Clipper	Relogios	Borda d'Água	Restantes Artigos	Tubos
3	26253	Cacem-Av B Amigos	0,00 €
4	26507	Santarem/ nascente	0,00 €
5	26800	Santarem/poente	0,00 €
6	27036	Almada V.R.-Norte	0,00 €
7	27037	Almada V.R.-Sul	0,00 €
8	27004	Pêro Pinheiro	0,00 €
9	27008	Ic 19-Poente	0,00 €
10	27078	Ramalhão	0,00 €
11	27114	Mealhada-Nascente	0,00 €
12	27115	Mealhada-Poente	0,00 €
13	27168	Santarem -Variante	0,00 €
14	27406	Duarte Pacheco	0,00 €
15	27311	A21-Mafra Nasc/N	0,00 €
16	27312	A21-Mafra Poen/S	0,00 €
17	27088	Aeroporto	0,00 €
18	27020	Albufeira V. R.	0,00 €
19	27010	Amadora	0,00 €
20	27011	Amadora II-Funch.	0,00 €
21	27006	Areeiro	0,00 €
22	27145	Braga-31 Janeiro	0,00 €
23	27182	Caneças	0,00 €
24	26264	Carcavelos	0,00 €
BP_MAIO_2022

Cell Formulas
Range	Formula
C3:C24	C3	=SUMPRODUCT((Sheet1!$D$8:$D$78)*(Sheet1!$I$8:$J$78)*(Sheet1!$A$8:$A$78=C$1)*(Sheet1!$H$6:$J$6=$C3))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C3:G3,F4:G217,C4:E215	Cell Value	<0	text	NO

 

[SanjayGMusafir]

Cell reference issue not macro

try

=SUMPRODUCT((Sheet1!$D$8:$D$78)*(Sheet1!$E$8:$H$78)*(Sheet1!$A$8:$A$78=C$1)*(Sheet1!$E$6:$H$6=$A3))

 

[himurah182]

Cell reference issue not macro

try

=SUMPRODUCT((Sheet1!$D$8:$D$78)*(Sheet1!$E$8:$H$78)*(Sheet1!$A$8:$A$78=C$1)*(Sheet1!$E$6:$H$6=$A3))

It’s perfect sir
Thank you for your help

 

[SanjayGMusafir]

It’s perfect sir
Thank you for your help

Finally - You also kept patience during the process.