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]

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?
View attachment 71170View attachment 71169View attachment 71171

Your question is somewhat confusing. Please explain it more clearly

Just to understand in Cell D8 of Picture 1 what exactly you want and from where you want that data to come?

 

[himurah182]

The table in picture 1 has a macro that copies every data in the d5:d75 range into column E, and everytime I fill it with newer data and use the macro again, the values that were already in column E move 1 column to the right. This table was designed to count the amount of products from each reference (range c8:c78) that were sold to each customer(d7).
D6- represents the customer's code
D5- represents the invoice number
The second picture has a range of clients and their respective codes. Also, it has 5 columns that correspond to the 5 types of products that we need to count.
What I want is the following: everytime I add new data in the table of picture 1 and use the macro to register it in Column E, I need the quantities of each product to multiply by their correspondent unit prices (picture 3) and sum them to their specific column in the table of picture 2, according to their type of product. For example, E8 would multiply by its unit price (3rd picture) and its result would be added to on c5 along with the other data which also belongs to this collumn.
I hope it made sense.
I pasted this again so you can see the ranges clearly.

 

[SanjayGMusafir]

The table in picture 1 has a macro that copies every data in the d5:d75 range into column E, and everytime I fill it with newer data and use the macro again, the values that were already in column E move 1 column to the right. This table was designed to count the amount of products from each reference (range c8:c78) that were sold to each customer(d7).
D6- represents the customer's code
D5- represents the invoice number
The second picture has a range of clients and their respective codes. Also, it has 5 columns that correspond to the 5 types of products that we need to count.
What I want is the following: everytime I add new data in the table of picture 1 and use the macro to register it in Column E, I need the quantities of each product to multiply by their correspondent unit prices (picture 3) and sum them to their specific column in the table of picture 2, according to their type of product. For example, E8 would multiply by its unit price (3rd picture) and its result would be added to on c5 along with the other data which also belongs to this collumn.
I hope it made sense.
I pasted this again so you can see the ranges clearly.
View attachment 71185

No, it still does not make sense. We are dealing with Excel not Pictures - We need cell address, sheet names. Moreover only you know which is Picture 1 and which is Pic3 because you uploaded it. Use XL2BB to upload sample data.

 

[himurah182]

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

Cell Formulas
Range	Formula
D6	D6	=IFNA(INDEX(Sheet2!B3:B215,MATCH(Sheet1!D7,Sheet2!C3:C215,0)),"")

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

calc3 (2).xlsm
	A	B	C	D
83				unit price
84	type 1	103581	RPBLACK2-BLACK ULTRA THIN 1,1/4	0,454
85		103582	RPBLACK3-BLACK KING SIZE	0,495
86		103583	BOLSA FILTROS REGULAR-8MM-30B-100F	0,482
87		103584	BOLSA FILTROS SLIM-6MM-34B-120F	0,433
88		103585	ISQUEIRO CLIPPER PEQ. CP22RH	0,607
89		103586	ISQUEIRO CLIPPER PEQ FUN	1,77
90		103587	ISQUERIRO CLIPPER GRD CP11RH	0,695
91		103902	ISQUEIRO CLIPPER CLASS.METAL LARG CAIXA	6,75
92		103903	BOLSA FILTROS LONG SLIM-6MM-30B-100F	0,557
93		103904	BOLSA FILTROS LONG REGULAR-8MM-20B-100F	0,557
94		105457	ISQUEIRO Clipper Classic POP CPR11H	1,755
95		106629	ISQUEIRO MINI CLIPPER TUBE	0,92
96		106630	PAPEL CLIPPER BLUE ULTRA THIN REGULAR	0,36
97		106631	PAPEL CLIPPER PURE ULTRA THIN REGULAR	0,36
98		106632	PAPEL CLIPPERPURE ULTRA THIN KSS	0,545
99		106633	PAPEL CLIPPER RED REGULAR CLASSIC	0,338
100		106634	PAPEL CLIPPER GREEN CUT CORNERS REGULAR	0,338
101		106636	MÁQUINA ENROLAR	1,697
102		106637	FILTROS POP UP EXTRA SLIM	0,685
103		106638	MEN-FILTROS POP UP EXTRA SLIM MENTOL	0,763
104		106639	FILTROS SLIM ORGANIC BOLSA	0,648
105		107670	SET KING SIZE SLIM+FILTROS DE PAPEL	0,86
106		116773	PAPEL RAW ORG HEMP KING SIZE SLIM	0,575
107		116775	PAPEL RAW CLASSIC KING SIZE SLIM	0,545
108		116774	PAPEL RAW ORG REGULAR	0,385
109		116776	FILTROS RAW ORGANIC SLIM	0,65
110	type 2	103588	RELÓGIO Medium Price	17,2
111		103589	RELÓGIO Low Price	13,75
112		103905	RELOGIO ANALOGICO	27,55
113		106640	SMARTWATCH SAMI	45,89
114	type 3	106506	CIGARREIRA CAIXA PLASTICO	2,65
115		106506	CIGARREIRA SILICONE	2,65
116		106661	Cigarreira Cartão	2,65
117		106506	CIGARREIRA CAIXA METAL	2,65
118		103906	LINHA PORTUGAL PORTA MOEDAS	3,39
119		103907	LINHA PORTUGAL ESTOJO	4,49
120		103908	LINHA PORTUGAL CARTEIRA 2	5,69
121		103909	LINHA PORTUGAL CARTEIRA	5,69
122		103910	CARTEIRA PRIMAVERA C/FECHO 119 BAND	5,65
123		103911	CARTEIRA PRIMAVERA GRANDE 110	4,49
124		103912	CARTEIRA PRIMAVERA PEQUEÑA 111	4,05
125		103913	MATRAFONA GIGANTE	11,45
126		103914	BLOCO NOTAS	1,61
127		103915	PULSERIRA PELE COM CAIXA	12,59
128		103916	LICOREIRA CORTIÇA	6,85
129		103917	PULSEIRAS CORTIÇA	6,85
130		103918	COLARES CORTIÇA	7,99
131		103919	CANETA CORTIÇA COM CAIXA	5,69
132		106507	PELUCHE TAM 1	10,29
133		106508	PELUCHE TAM 3	16,05
134		106509	OCULOS SOL	15,69
135		106641	CARTAS JOGAR PLÁSTICO	5,2
136		106642	CARTEIRA GB SRA PELE	25,25
137		106643	CARTEIRA GB HM PELE-PEQ	14,89
138		106644	CARTEIRA GB HM PELE- GR	17,2
139		106645	SACA ROLHAS CORTIÇA	2,85
140		106654	CIGARREIRAS CORTIÇA	5,75
141		106655	MINIATURA V. PORTO/LICOR BEIRÃO	3,45
142		106656	CONJ 2 MINIATURAS PORTO+BEIRÃO	5,2
143		106657	PORTA MOEDAS PEQ CORTIÇA-VÁRIOS MODELOS	4,55
144		106658	PORTA MOEDAS DA AVÓ-CORTIÇA	7,9
145		106659	PORTA MOEDAS HM -CORTIÇA	9,15
146		106660	PORTA MOEDAS MOCHILA CORTIÇA	6,05
147		107671	PUZZLES- SORTIDOS	3,45
148		107672	JOGO TABULEIRO-SORTIDO	6,3
149		107673	OVO DINOSSAURO	3,16
150		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		115903	TUBOS KORONA 300 UND	1,122
154		115902	TUBOS KORONA 500 UND	2,02
155
Sheet1

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
13	27168	Santarem -Variante
14	27406	Duarte Pacheco
15	27311	A21-Mafra Nasc/N
16	27312	A21-Mafra Poen/S
17	27088	Aeroporto
18	27020	Albufeira V. R.
19	27010	Amadora
20	27011	Amadora II-Funch.
21	27006	Areeiro
22	27145	Braga-31 Janeiro
23	27182	Caneças
24	26264	Carcavelos
25	26266	Carnaxide
26	27116	Espinho
27	27013	Estoril-St António
28	27026	Faro Norte
29	27196	Faro Sul
30	27140	Gaia
31	27024	Loulé
32	26709	Lourel Este
33	26808	Lourel Oeste
34	27152	Maia
35	27330	Maia Este-A 41
36	27332	Maia Oeste-A 41
37	27315	Ovar Nascente
38	27316	Ovar Poente
39	26536	Santana-Sesimbra
40	27007	Tamariz
41	26442	Valadares-Nascente
42	26811	Valadares-Poente
43	27029	Faro-Hayward
44	26008	Aveiro-Este
45	26802	Aveiro-Oeste
46	26376	Mercês
47	26620	Fluvial-Porto
48	27173	Quinta do Conde
49	26156	Quarteira-Norte
50	26804	Quarteira -Sul
51	26636	Cascais-A. Costa
52	27144	Cascais-B. Rosário
53	27126	Santa Maria da Feira
54	27017	Porto Combatentes
55	27407	Prior Velho
56	27325	Matosinhos-Custóias
57	27000	Torres Vedras
58	27045	Setúbal-B.J.Caraça
59	27044	Setúbal-Ciprestes
60	27291	Setúbal-Monte Belo
61	27432	Oeiras
62	27434	Perafita/Matosinhos
63	27430	Vale Figueira
64	27047	Setúbal/Nac10-Norte
65	27201	Setúbal/Nac10-Sul
66	27417	Bela Vista-Lisboa
67	27065	Domingos Sequeira
68	26154	Portimão/Raminha
69	27253	Tavira/Horta Carmo
70	26014	Granja
71	27414	Loures/Flamenga
72	27500	Perafita/Nó A 28
73	26427	Porto 5 Outubro
74	27277	Viana do Castelo
75	27421	Armação Pêra
76	26337	Sintra Miradouro
77	27125	OL.AZ.-Ant.Bernardo
78	27060	Peniche
79	26803	Porches-Sul
80	26149	Porches - Norte
81	27048	Fernão Ferro S/N
82	27049	Fernão Ferro N/S
83	27236	Almancil
84	27302	Ponte de Sôr
85	27135	Carvalhos P.A.
86	27123	Mala Posta-Oeste
87	27124	Mala Posta-Este
88	27153	Fafe
89	26392	Gondomar/Fânzeres
90	27171	Portuzelo
91	27428	Viseu/Abravezes
92	27287	Ponte da Barca
93	26604	Viseu/Via Sacra
94	27055	Montemor-o Novo
95	27003	Marinha Grande
96	27146	Braga/I. Conçeição
97	27246	Braga/Palmeira
98	27489	Mafra
99	27102	Cantanhede
100	27306	Mealhada/Comba
101	27238	Irivo
102	27361	Odivelas
103	26322	Mem Martins
104	27490	Boliqueime
105	27249	Pataias-Poente
106	27248	Pataias-Nascente
107	27021	Monte Gordo
108	27113	O.Az-Ant Jose Alm.
109	27150	Águas Santas
110	27183	Silveira
111	27031	Monchique
112	27516	Almancil/Pereiras
113	27095	Leiria
114	27541	A2-Seixal
115	27109	S. João da Madeira
116	27327	Cartaxo
117	27127	Agueda
118	27156	Famalicão
119	27544	S Mamede Infesta
120	27501	Marinhais
121	27189	Beja Variante
122	27502	Guarda
123	27543	Sintra-Av. Cascais
124	27032	Quarteira-Av Ceuta
125	27511	Sintra- Linhó
126	27477	Porto Alto-Nascente
127	27478	Porto Alto-Poente
128	27424	Cercal
129	27510	Portalegre
130	27179	Mortágua
131	27043	Portimão-V 6
132	27550	Palmela
133	27098	P Alto -Cruzamento
134	27270	C1-Ponte da Couraça
135	27345	Casal Pinheiro
136	27195	Chão da Feira
137	26394	Via Norte-ESTE
138	26809	Via Norte-OESTE
139	27457	Vizela Norte-Infias
140	27326	Vila Boa - Barcelos
141	27255	Santa Barbara Nexe
142	27266	V. Real-Constantim
143	27181	IP4 Norte-Mirandela
144	27233	IP4 Sul-Mirandela
145	27437	Barreiro-Vila Chã
146	27524	Tomar
147	27014	V.N.Gaia- Fojo
148	26148	BP Penina Sul
149	27573	Entroncamento
150	27130	Aguada de Baixo/Este
151	27131	Aguada de Baixo/Oeste
152	26398	Marco de Canavezes/N
153	27552	Leiria-Azóia
154	27190	Bustos
155	27527	Venda do Pinheiro
156	27523	P . Novo-Cascalheira
157	27431	Évora-Mercado
158	27574	Alcobaça-Variante
159	27136	IP5 Mangualde (Norte)
160	27137	IP5 Mangualde( Sul)
161	27435	Cantanhede-Murtede
162	27147	Alfena
163	27493	Guimarães Creixomil
164	27585	Ílhavo
165	26708	Nazaré Pataias
166	27153	Portimão-Malata
167	27532	Vila do Conde-Mindelo
168	27459	Paços Ferreira-Variante
169	27204	P.Ferreira João XXIII-Este
170	27304	P.Ferreira João XXIII-Oeste
171	27295	S. Maria Feira(GIÃO)
172	27039	Casal do Marco-Norte
173	27040	Casal do Marco-Sul
174	27531	Porto-Circunvalação
175	27056	Pegões Sul
176	27451	Cova do Gato
177	27328	Lagoinha- Palmela
178	27436	Cadaval- Palhoça
179	27058	Figueira da Foz
180	27317	Montijo- Rotunda
181	27546	Lagoa-Parchal
182	27588	Entroncamento Centro
183	27015	Via Norte-Amieira/Este
184	27016	Via Norte- Amieira/Oeste
185	27519	BP Vila d`Este
186	27463	Vila Real- Norte/Lamares
187	27464	Vila Real-Sul /Lamares
188	27261	Lousada
189	27296	Maia-Ardegães
190	27303	Vila Velha de Ródão
191	27307	Felgueiras-Varziela
192	27163	Lixa
193	27064	Coimbra
194	27099	Lisboa-Doca Bom Sucesso
195	27301	Paços de Ferreira-Seroa
196	27438	Montijo- Lançada
197	27603	Figueira da Foz-Tavarede
198	27597	Odivelas-Ramada
199	27133	Carvalhos-Perosinho
200	27310	Ponte de Lima-Arcozelo
201	27244	Vieira do Minho
202	27604	Figueira da Foz-S. Pedro
203	27545	Maia ZI
204	27333	Esposende- Fão
205	27479	Castelo Branco-Montalvão
206	27273	Cartaxo-Vila Chã
207	27247	Loures -Sete Casas
208	27207	Marinha Grande Embra
209	27030	Barreiro- Alto da Telha
210	27360	Barreiro-D.João II
211	27154	Valongo (Campo)
212	27598	Guimarães Silvares
213	27583	Grândola Vila
214	27034	BP Grândola - Oeste
215	27587	Vilamoura Av. VM 21
216
217
218		Total	0,00 €	0,00 €	0,00 €	0,00 €	0,00 €
BP MAIO 2022

Cell Formulas
Range	Formula
C218:G218	C218	=SUM(C3:C217)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C73:E73	Cell Value	<0	text	NO
E39,C39,C3:G3,G120,G46,C4:E38,F4:G45,C40:E64,F47:G119,F121:G217	Cell Value	<0	text	NO

 

[SanjayGMusafir]

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

Cell Formulas
Range	Formula
D6	D6	=IFNA(INDEX(Sheet2!B3:B215,MATCH(Sheet1!D7,Sheet2!C3:C215,0)),"")

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

calc3 (2).xlsm
	A	B	C	D
83				unit price
84	type 1	103581	RPBLACK2-BLACK ULTRA THIN 1,1/4	0,454
85		103582	RPBLACK3-BLACK KING SIZE	0,495
86		103583	BOLSA FILTROS REGULAR-8MM-30B-100F	0,482
87		103584	BOLSA FILTROS SLIM-6MM-34B-120F	0,433
88		103585	ISQUEIRO CLIPPER PEQ. CP22RH	0,607
89		103586	ISQUEIRO CLIPPER PEQ FUN	1,77
90		103587	ISQUERIRO CLIPPER GRD CP11RH	0,695
91		103902	ISQUEIRO CLIPPER CLASS.METAL LARG CAIXA	6,75
92		103903	BOLSA FILTROS LONG SLIM-6MM-30B-100F	0,557
93		103904	BOLSA FILTROS LONG REGULAR-8MM-20B-100F	0,557
94		105457	ISQUEIRO Clipper Classic POP CPR11H	1,755
95		106629	ISQUEIRO MINI CLIPPER TUBE	0,92
96		106630	PAPEL CLIPPER BLUE ULTRA THIN REGULAR	0,36
97		106631	PAPEL CLIPPER PURE ULTRA THIN REGULAR	0,36
98		106632	PAPEL CLIPPERPURE ULTRA THIN KSS	0,545
99		106633	PAPEL CLIPPER RED REGULAR CLASSIC	0,338
100		106634	PAPEL CLIPPER GREEN CUT CORNERS REGULAR	0,338
101		106636	MÁQUINA ENROLAR	1,697
102		106637	FILTROS POP UP EXTRA SLIM	0,685
103		106638	MEN-FILTROS POP UP EXTRA SLIM MENTOL	0,763
104		106639	FILTROS SLIM ORGANIC BOLSA	0,648
105		107670	SET KING SIZE SLIM+FILTROS DE PAPEL	0,86
106		116773	PAPEL RAW ORG HEMP KING SIZE SLIM	0,575
107		116775	PAPEL RAW CLASSIC KING SIZE SLIM	0,545
108		116774	PAPEL RAW ORG REGULAR	0,385
109		116776	FILTROS RAW ORGANIC SLIM	0,65
110	type 2	103588	RELÓGIO Medium Price	17,2
111		103589	RELÓGIO Low Price	13,75
112		103905	RELOGIO ANALOGICO	27,55
113		106640	SMARTWATCH SAMI	45,89
114	type 3	106506	CIGARREIRA CAIXA PLASTICO	2,65
115		106506	CIGARREIRA SILICONE	2,65
116		106661	Cigarreira Cartão	2,65
117		106506	CIGARREIRA CAIXA METAL	2,65
118		103906	LINHA PORTUGAL PORTA MOEDAS	3,39
119		103907	LINHA PORTUGAL ESTOJO	4,49
120		103908	LINHA PORTUGAL CARTEIRA 2	5,69
121		103909	LINHA PORTUGAL CARTEIRA	5,69
122		103910	CARTEIRA PRIMAVERA C/FECHO 119 BAND	5,65
123		103911	CARTEIRA PRIMAVERA GRANDE 110	4,49
124		103912	CARTEIRA PRIMAVERA PEQUEÑA 111	4,05
125		103913	MATRAFONA GIGANTE	11,45
126		103914	BLOCO NOTAS	1,61
127		103915	PULSERIRA PELE COM CAIXA	12,59
128		103916	LICOREIRA CORTIÇA	6,85
129		103917	PULSEIRAS CORTIÇA	6,85
130		103918	COLARES CORTIÇA	7,99
131		103919	CANETA CORTIÇA COM CAIXA	5,69
132		106507	PELUCHE TAM 1	10,29
133		106508	PELUCHE TAM 3	16,05
134		106509	OCULOS SOL	15,69
135		106641	CARTAS JOGAR PLÁSTICO	5,2
136		106642	CARTEIRA GB SRA PELE	25,25
137		106643	CARTEIRA GB HM PELE-PEQ	14,89
138		106644	CARTEIRA GB HM PELE- GR	17,2
139		106645	SACA ROLHAS CORTIÇA	2,85
140		106654	CIGARREIRAS CORTIÇA	5,75
141		106655	MINIATURA V. PORTO/LICOR BEIRÃO	3,45
142		106656	CONJ 2 MINIATURAS PORTO+BEIRÃO	5,2
143		106657	PORTA MOEDAS PEQ CORTIÇA-VÁRIOS MODELOS	4,55
144		106658	PORTA MOEDAS DA AVÓ-CORTIÇA	7,9
145		106659	PORTA MOEDAS HM -CORTIÇA	9,15
146		106660	PORTA MOEDAS MOCHILA CORTIÇA	6,05
147		107671	PUZZLES- SORTIDOS	3,45
148		107672	JOGO TABULEIRO-SORTIDO	6,3
149		107673	OVO DINOSSAURO	3,16
150		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		115903	TUBOS KORONA 300 UND	1,122
154		115902	TUBOS KORONA 500 UND	2,02
155
Sheet1

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
13	27168	Santarem -Variante
14	27406	Duarte Pacheco
15	27311	A21-Mafra Nasc/N
16	27312	A21-Mafra Poen/S
17	27088	Aeroporto
18	27020	Albufeira V. R.
19	27010	Amadora
20	27011	Amadora II-Funch.
21	27006	Areeiro
22	27145	Braga-31 Janeiro
23	27182	Caneças
24	26264	Carcavelos
25	26266	Carnaxide
26	27116	Espinho
27	27013	Estoril-St António
28	27026	Faro Norte
29	27196	Faro Sul
30	27140	Gaia
31	27024	Loulé
32	26709	Lourel Este
33	26808	Lourel Oeste
34	27152	Maia
35	27330	Maia Este-A 41
36	27332	Maia Oeste-A 41
37	27315	Ovar Nascente
38	27316	Ovar Poente
39	26536	Santana-Sesimbra
40	27007	Tamariz
41	26442	Valadares-Nascente
42	26811	Valadares-Poente
43	27029	Faro-Hayward
44	26008	Aveiro-Este
45	26802	Aveiro-Oeste
46	26376	Mercês
47	26620	Fluvial-Porto
48	27173	Quinta do Conde
49	26156	Quarteira-Norte
50	26804	Quarteira -Sul
51	26636	Cascais-A. Costa
52	27144	Cascais-B. Rosário
53	27126	Santa Maria da Feira
54	27017	Porto Combatentes
55	27407	Prior Velho
56	27325	Matosinhos-Custóias
57	27000	Torres Vedras
58	27045	Setúbal-B.J.Caraça
59	27044	Setúbal-Ciprestes
60	27291	Setúbal-Monte Belo
61	27432	Oeiras
62	27434	Perafita/Matosinhos
63	27430	Vale Figueira
64	27047	Setúbal/Nac10-Norte
65	27201	Setúbal/Nac10-Sul
66	27417	Bela Vista-Lisboa
67	27065	Domingos Sequeira
68	26154	Portimão/Raminha
69	27253	Tavira/Horta Carmo
70	26014	Granja
71	27414	Loures/Flamenga
72	27500	Perafita/Nó A 28
73	26427	Porto 5 Outubro
74	27277	Viana do Castelo
75	27421	Armação Pêra
76	26337	Sintra Miradouro
77	27125	OL.AZ.-Ant.Bernardo
78	27060	Peniche
79	26803	Porches-Sul
80	26149	Porches - Norte
81	27048	Fernão Ferro S/N
82	27049	Fernão Ferro N/S
83	27236	Almancil
84	27302	Ponte de Sôr
85	27135	Carvalhos P.A.
86	27123	Mala Posta-Oeste
87	27124	Mala Posta-Este
88	27153	Fafe
89	26392	Gondomar/Fânzeres
90	27171	Portuzelo
91	27428	Viseu/Abravezes
92	27287	Ponte da Barca
93	26604	Viseu/Via Sacra
94	27055	Montemor-o Novo
95	27003	Marinha Grande
96	27146	Braga/I. Conçeição
97	27246	Braga/Palmeira
98	27489	Mafra
99	27102	Cantanhede
100	27306	Mealhada/Comba
101	27238	Irivo
102	27361	Odivelas
103	26322	Mem Martins
104	27490	Boliqueime
105	27249	Pataias-Poente
106	27248	Pataias-Nascente
107	27021	Monte Gordo
108	27113	O.Az-Ant Jose Alm.
109	27150	Águas Santas
110	27183	Silveira
111	27031	Monchique
112	27516	Almancil/Pereiras
113	27095	Leiria
114	27541	A2-Seixal
115	27109	S. João da Madeira
116	27327	Cartaxo
117	27127	Agueda
118	27156	Famalicão
119	27544	S Mamede Infesta
120	27501	Marinhais
121	27189	Beja Variante
122	27502	Guarda
123	27543	Sintra-Av. Cascais
124	27032	Quarteira-Av Ceuta
125	27511	Sintra- Linhó
126	27477	Porto Alto-Nascente
127	27478	Porto Alto-Poente
128	27424	Cercal
129	27510	Portalegre
130	27179	Mortágua
131	27043	Portimão-V 6
132	27550	Palmela
133	27098	P Alto -Cruzamento
134	27270	C1-Ponte da Couraça
135	27345	Casal Pinheiro
136	27195	Chão da Feira
137	26394	Via Norte-ESTE
138	26809	Via Norte-OESTE
139	27457	Vizela Norte-Infias
140	27326	Vila Boa - Barcelos
141	27255	Santa Barbara Nexe
142	27266	V. Real-Constantim
143	27181	IP4 Norte-Mirandela
144	27233	IP4 Sul-Mirandela
145	27437	Barreiro-Vila Chã
146	27524	Tomar
147	27014	V.N.Gaia- Fojo
148	26148	BP Penina Sul
149	27573	Entroncamento
150	27130	Aguada de Baixo/Este
151	27131	Aguada de Baixo/Oeste
152	26398	Marco de Canavezes/N
153	27552	Leiria-Azóia
154	27190	Bustos
155	27527	Venda do Pinheiro
156	27523	P . Novo-Cascalheira
157	27431	Évora-Mercado
158	27574	Alcobaça-Variante
159	27136	IP5 Mangualde (Norte)
160	27137	IP5 Mangualde( Sul)
161	27435	Cantanhede-Murtede
162	27147	Alfena
163	27493	Guimarães Creixomil
164	27585	Ílhavo
165	26708	Nazaré Pataias
166	27153	Portimão-Malata
167	27532	Vila do Conde-Mindelo
168	27459	Paços Ferreira-Variante
169	27204	P.Ferreira João XXIII-Este
170	27304	P.Ferreira João XXIII-Oeste
171	27295	S. Maria Feira(GIÃO)
172	27039	Casal do Marco-Norte
173	27040	Casal do Marco-Sul
174	27531	Porto-Circunvalação
175	27056	Pegões Sul
176	27451	Cova do Gato
177	27328	Lagoinha- Palmela
178	27436	Cadaval- Palhoça
179	27058	Figueira da Foz
180	27317	Montijo- Rotunda
181	27546	Lagoa-Parchal
182	27588	Entroncamento Centro
183	27015	Via Norte-Amieira/Este
184	27016	Via Norte- Amieira/Oeste
185	27519	BP Vila d`Este
186	27463	Vila Real- Norte/Lamares
187	27464	Vila Real-Sul /Lamares
188	27261	Lousada
189	27296	Maia-Ardegães
190	27303	Vila Velha de Ródão
191	27307	Felgueiras-Varziela
192	27163	Lixa
193	27064	Coimbra
194	27099	Lisboa-Doca Bom Sucesso
195	27301	Paços de Ferreira-Seroa
196	27438	Montijo- Lançada
197	27603	Figueira da Foz-Tavarede
198	27597	Odivelas-Ramada
199	27133	Carvalhos-Perosinho
200	27310	Ponte de Lima-Arcozelo
201	27244	Vieira do Minho
202	27604	Figueira da Foz-S. Pedro
203	27545	Maia ZI
204	27333	Esposende- Fão
205	27479	Castelo Branco-Montalvão
206	27273	Cartaxo-Vila Chã
207	27247	Loures -Sete Casas
208	27207	Marinha Grande Embra
209	27030	Barreiro- Alto da Telha
210	27360	Barreiro-D.João II
211	27154	Valongo (Campo)
212	27598	Guimarães Silvares
213	27583	Grândola Vila
214	27034	BP Grândola - Oeste
215	27587	Vilamoura Av. VM 21
216
217
218		Total	0,00 €	0,00 €	0,00 €	0,00 €	0,00 €
BP MAIO 2022

Cell Formulas
Range	Formula
C218:G218	C218	=SUM(C3:C217)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C73:E73	Cell Value	<0	text	NO
E39,C39,C3:G3,G120,G46,C4:E38,F4:G45,C40:E64,F47:G119,F121:G217	Cell Value	<0	text	NO

Wonderful effort. Now your data makes all the sense.

What I have understood so far that in the sheet BP MAIO 2022 you want to populate some calculation from C3 to G3. Right?

Now explain me what exactly you want. Then I shall explain few things to you. The way you are keeping data in Excel needs slight improvement. That I will explain to you, once I'm clear what eaxctly you want to do.

Thanks for your patience while, I'm patiently trying to understand your need.

 

[himurah182]

In the first table I have a macro that copies the data in column D (d5:d78) and pastes it in column E (e5:e78). Everytime I have new data I paste it on column E, and what was there before moves 1 column to the right.
The data of the first table corresponds to the quantities of each specific product(c8:c78) that were sold to each customer(d7 for example).
My problem is the following: I want to know how much did any of my customers spend on each type of products (type 1, type 2, type 3, etc). For that to happen, I need the values from each column from table 1(starting in column E) to be multiplied by their respective unit price and to be pasted and summed in the cell that corresponds to their type of product and the customer that they were sold to.

 

[SanjayGMusafir]

In the first table I have a macro that copies the data in column D (d5:d78) and pastes it in column E (e5:e78). Everytime I have new data I paste it on column E, and what was there before moves 1 column to the right.
The data of the first table corresponds to the quantities of each specific product(c8:c78) that were sold to each customer(d7 for example).
My problem is the following: I want to know how much did any of my customers spend on each type of products (type 1, type 2, type 3, etc). For that to happen, I need the values from each column from table 1(starting in column E) to be multiplied by their respective unit price and to be pasted and summed in the cell that corresponds to their type of product and the customer that they were sold to.

Now understood your question just give me few minutes and I come back with the solution. That is actually not that difficult. Just needs some data improvement. I just revert.

 

[himurah182]

Now understood your question just give me few minutes and I come back with the solution. That is actually not that difficult. Just needs some data improvement. I just revert.

Ok cheers

 

[SanjayGMusafir]

Ok cheers

Please look at the solution below -

What I did just to understand your data and get it done faster, I took all the data on i sheet itself - so when you are copying any formula remember to give the right references of sheet and columns. Only that way formulas will work the intended way for you.

Then, IMPORTANT - You have merged columns that carry Type 1, 2 3 & 4 - They all need to be unmerged and filled down - I have highlighted the changes made to column in Blue.

Another thing, I added a helper column of Unit price (shown in Pink) to pull Prices from another table.

Rest is all populating formulae as per the need. May Check

Best wishes

All Records.xlsb
	A	B	C	D	E	F	G	H
1
2
3
4
5			Fatura
6			Where code		27078	27078	27037	26253
7		Plu	Posto	Unit Price	Ramalhão	Ramalhão	Almada V.R.-Sul	Cacem-Av B Amigos
8	type 1	103581	RPBLACK2-BLACK ULTRA THIN 1,1/4	0.454				1
9	type 1	103582	RPBLACK3-BLACK KING SIZE	0.495
10	type 1	103583	BOLSA FILTROS REGULAR-8MM-30B-100F	0.482	45	45	45
11	type 1	103584	BOLSA FILTROS SLIM-6MM-34B-120F	0.433
12	type 1	103585	ISQUEIRO CLIPPER PEQ. CP22RH	0.607
13	type 1	103586	ISQUEIRO CLIPPER PEQ FUN	1.77
14	type 1	103587	ISQUERIRO CLIPPER GRD CP11RH	0.695
15	type 1	103902	ISQUEIRO CLIPPER CLASS.METAL LARG CAIXA	6.75
16	type 1	103903	BOLSA FILTROS LONG SLIM-6MM-30B-100F	0.557	5	5	5
17	type 1	103904	BOLSA FILTROS LONG REGULAR-8MM-20B-100F	13.75
18	type 1	105457	ISQUEIRO Clipper Classic POP CPR11H	5.69
19	type 1	106629	ISQUEIRO MINI CLIPPER TUBE	15.69
20	type 1	106630	PAPEL CLIPPER BLUE ULTRA THIN REGULAR	15.69
21	type 1	106631	PAPEL CLIPPER PURE ULTRA THIN REGULAR	15.69
22	type 1	106632	PAPEL CLIPPERPURE ULTRA THIN KSS	1.6	5	5	5
23	type 1	106633	PAPEL CLIPPER RED REGULAR CLASSIC	15.69
24	type 1	106634	PAPEL CLIPPER GREEN CUT CORNERS REGULAR	15.69
25	type 1	106636	MÁQUINA ENROLAR	15.69
26	type 1	106637	FILTROS POP UP EXTRA SLIM	15.69
27	type 1	106638	MEN-FILTROS POP UP EXTRA SLIM MENTOL	15.69
28	type 1	106639	FILTROS SLIM ORGANIC BOLSA	15.69
29	type 1	107670	SET KING SIZE SLIM+FILTROS DE PAPEL	6.05
30	type 1	116773	PAPEL RAW ORG HEMP KING SIZE SLIM	2.02
31	type 1	116775	PAPEL RAW CLASSIC KING SIZE SLIM	2.02
32	type 1	116774	PAPEL RAW ORG REGULAR	2.02
33	type 1	116776	FILTROS RAW ORGANIC SLIM	2.02
34	Type 2	103588	RELÓGIO Medium Price	17.2
35	Type 2	103589	RELÓGIO Low Price	13.75	5	5	5
36	Type 2	103905	RELOGIO ANALOGICO	27.55
37	Type 2	106640	SMARTWATCH SAMI	15.69
38	Type 3	106506	CIGARREIRA CAIXA PLASTICO	5.69
39	Type 3	106506	CIGARREIRA SILICONE	5.69
40	Type 3	106661	Cigarreira Cartão	1.6
41	Type 3	106506	CIGARREIRA CAIXA METAL	5.69
42	Type 3	103906	LINHA PORTUGAL PORTA MOEDAS	3.39
43	Type 3	103907	LINHA PORTUGAL ESTOJO	4.49
44	Type 3	103908	LINHA PORTUGAL CARTEIRA 2	5.69
45	Type 3	103909	LINHA PORTUGAL CARTEIRA	5.69
46	Type 3	103910	CARTEIRA PRIMAVERA C/FECHO 119 BAND	5.65
47	Type 3	103911	CARTEIRA PRIMAVERA GRANDE 110	4.49
48	Type 3	103912	CARTEIRA PRIMAVERA PEQUEÑA 111	4.05	5	5	5
49	Type 3	103913	MATRAFONA GIGANTE	11.45
50	Type 3	103914	BLOCO NOTAS	1.61
51	Type 3	103915	PULSERIRA PELE COM CAIXA	12.59
52	Type 3	103916	LICOREIRA CORTIÇA	6.85
53	Type 3	103917	PULSEIRAS CORTIÇA	6.85
54	Type 3	103918	COLARES CORTIÇA	7.99	5	5	5
55	Type 3	103919	CANETA CORTIÇA COM CAIXA	5.69
56	Type 3	106507	PELUCHE TAM 1	10.29
57	Type 3	106508	PELUCHE TAM 3	16.05
58	Type 3	106509	OCULOS SOL	1.6
59	Type 3	106641	CARTAS JOGAR PLÁSTICO	5.2
60	Type 3	106642	CARTEIRA GB SRA PELE	25.25
61	Type 3	106643	CARTEIRA GB HM PELE-PEQ	14.89
62	Type 3	106644	CARTEIRA GB HM PELE- GR	17.2
63	Type 3	106645	SACA ROLHAS CORTIÇA	2.85
64	Type 3	106654	CIGARREIRAS CORTIÇA	5.75	5	5	5
65	Type 3	106655	MINIATURA V. PORTO/LICOR BEIRÃO	3.45
66	Type 3	106656	CONJ 2 MINIATURAS PORTO+BEIRÃO	5.2
67	Type 3	106657	PORTA MOEDAS PEQ CORTIÇA-VÁRIOS MODELOS	1.6
68	Type 3	106658	PORTA MOEDAS DA AVÓ-CORTIÇA	7.9
69	Type 3	106659	PORTA MOEDAS HM -CORTIÇA	9.15
70	Type 3	106660	PORTA MOEDAS MOCHILA CORTIÇA	6.05
71	Type 3	107671	PUZZLES- SORTIDOS	3.45
72	Type 3	107672	JOGO TABULEIRO-SORTIDO	1.6
73	Type 3	107673	OVO DINOSSAURO	3.16
74	Type 3	107674	CONJ CORTIÇA	1.6
75	type 4	103804	BORDA D'ÁGUA	1.6	5	5	5
76	type 5	114755	TUBOS T&T 500 UND	2.02
77	type 5	115903	TUBOS KORONA 300 UND	2.02
78	type 5	115902	TUBOS KORONA 500 UND	2.02	5	5	5
Sheet2

Cell Formulas
Range	Formula
E6	E6	=IFNA(INDEX(O3:O215,MATCH(E7,P3:P215,0)),"")
D8:D78	D8	=LOOKUP(B8,K2:K72,M2:M72)

All Records.xlsb
	J	K	L	M
1				unit price
2	type 1	103581	RPBLACK2-BLACK ULTRA THIN 1,1/4	0.454
3	type 1	103582	RPBLACK3-BLACK KING SIZE	0.495
4	type 1	103583	BOLSA FILTROS REGULAR-8MM-30B-100F	0.482
5	type 1	103584	BOLSA FILTROS SLIM-6MM-34B-120F	0.433
6	type 1	103585	ISQUEIRO CLIPPER PEQ. CP22RH	0.607
7	type 1	103586	ISQUEIRO CLIPPER PEQ FUN	1.77
8	type 1	103587	ISQUERIRO CLIPPER GRD CP11RH	0.695
9	type 1	103902	ISQUEIRO CLIPPER CLASS.METAL LARG CAIXA	6.75
10	type 1	103903	BOLSA FILTROS LONG SLIM-6MM-30B-100F	0.557
11	type 1	103904	BOLSA FILTROS LONG REGULAR-8MM-20B-100F	0.557
12	type 1	105457	ISQUEIRO Clipper Classic POP CPR11H	1.755
13	type 1	106629	ISQUEIRO MINI CLIPPER TUBE	0.92
14	type 1	106630	PAPEL CLIPPER BLUE ULTRA THIN REGULAR	0.36
15	type 1	106631	PAPEL CLIPPER PURE ULTRA THIN REGULAR	0.36
16	type 1	106632	PAPEL CLIPPERPURE ULTRA THIN KSS	0.545
17	type 1	106633	PAPEL CLIPPER RED REGULAR CLASSIC	0.338
18	type 1	106634	PAPEL CLIPPER GREEN CUT CORNERS REGULAR	0.338
19	type 1	106636	MÁQUINA ENROLAR	1.697
20	type 1	106637	FILTROS POP UP EXTRA SLIM	0.685
21	type 1	106638	MEN-FILTROS POP UP EXTRA SLIM MENTOL	0.763
22	type 1	106639	FILTROS SLIM ORGANIC BOLSA	0.648
23	type 1	107670	SET KING SIZE SLIM+FILTROS DE PAPEL	0.86
24	type 1	116773	PAPEL RAW ORG HEMP KING SIZE SLIM	0.575
25	type 1	116775	PAPEL RAW CLASSIC KING SIZE SLIM	0.545
26	type 1	116774	PAPEL RAW ORG REGULAR	0.385
27	type 1	116776	FILTROS RAW ORGANIC SLIM	0.65
28	Type 2	103588	RELÓGIO Medium Price	17.2
29	Type 2	103589	RELÓGIO Low Price	13.75
30	Type 2	103905	RELOGIO ANALOGICO	27.55
31	Type 2	106640	SMARTWATCH SAMI	45.89
32	Type 3	106506	CIGARREIRA CAIXA PLASTICO	2.65
33	Type 3	106506	CIGARREIRA SILICONE	2.65
34	Type 3	106661	Cigarreira Cartão	2.65
35	Type 3	106506	CIGARREIRA CAIXA METAL	2.65
36	Type 3	103906	LINHA PORTUGAL PORTA MOEDAS	3.39
37	Type 3	103907	LINHA PORTUGAL ESTOJO	4.49
38	Type 3	103908	LINHA PORTUGAL CARTEIRA 2	5.69
39	Type 3	103909	LINHA PORTUGAL CARTEIRA	5.69
40	Type 3	103910	CARTEIRA PRIMAVERA C/FECHO 119 BAND	5.65
41	Type 3	103911	CARTEIRA PRIMAVERA GRANDE 110	4.49
42	Type 3	103912	CARTEIRA PRIMAVERA PEQUEÑA 111	4.05
43	Type 3	103913	MATRAFONA GIGANTE	11.45
44	Type 3	103914	BLOCO NOTAS	1.61
45	Type 3	103915	PULSERIRA PELE COM CAIXA	12.59
46	Type 3	103916	LICOREIRA CORTIÇA	6.85
47	Type 3	103917	PULSEIRAS CORTIÇA	6.85
48	Type 3	103918	COLARES CORTIÇA	7.99
49	Type 3	103919	CANETA CORTIÇA COM CAIXA	5.69
50	Type 3	106507	PELUCHE TAM 1	10.29
51	Type 3	106508	PELUCHE TAM 3	16.05
52	Type 3	106509	OCULOS SOL	15.69
53	Type 3	106641	CARTAS JOGAR PLÁSTICO	5.2
54	Type 3	106642	CARTEIRA GB SRA PELE	25.25
55	Type 3	106643	CARTEIRA GB HM PELE-PEQ	14.89
56	Type 3	106644	CARTEIRA GB HM PELE- GR	17.2
57	Type 3	106645	SACA ROLHAS CORTIÇA	2.85
58	Type 3	106654	CIGARREIRAS CORTIÇA	5.75
59	Type 3	106655	MINIATURA V. PORTO/LICOR BEIRÃO	3.45
60	Type 3	106656	CONJ 2 MINIATURAS PORTO+BEIRÃO	5.2
61	Type 3	106657	PORTA MOEDAS PEQ CORTIÇA-VÁRIOS MODELOS	4.55
62	Type 3	106658	PORTA MOEDAS DA AVÓ-CORTIÇA	7.9
63	Type 3	106659	PORTA MOEDAS HM -CORTIÇA	9.15
64	Type 3	106660	PORTA MOEDAS MOCHILA CORTIÇA	6.05
65	Type 3	107671	PUZZLES- SORTIDOS	3.45
66	Type 3	107672	JOGO TABULEIRO-SORTIDO	6.3
67	Type 3	107673	OVO DINOSSAURO	3.16
68	Type 3	107674	CONJ CORTIÇA	11.45
69	type 4	103804	BORDA D'ÁGUA	1.6
70	type 5	114755	TUBOS T&T 500 UND	2.02
71	type 5	115903	TUBOS KORONA 300 UND	1.122
72	type 5	115902	TUBOS KORONA 500 UND	2.02
Sheet2

All Records.xlsb
	O	P	Q	R	S	T	U
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.45
4	26507	Santarem/ nascente
5	26800	Santarem/poente
6	27036	Almada V.R.-Norte
7	27037	Almada V.R.-Sul	32.48	68.75	88.95	8.00	10.10
8	27004	Pêro Pinheiro
9	27008	Ic 19-Poente
10	27078	Ramalhão	32.48	68.75	88.95	8.00	10.10
11	27114	Mealhada-Nascente
12	27115	Mealhada-Poente
13	27168	Santarem -Variante
Sheet2

Cell Formulas
Range	Formula
Q3:U13	Q3	=IF(SUMPRODUCT($D$8:$D$78*$F$8:$H$78*($A$8:$A$78=Q$1)*($F$6:$H$6=$O3))=0,"",SUMPRODUCT($D$8:$D$78*$F$8:$H$78*($A$8:$A$78=Q$1)*($F$6:$H$6=$O3)))