Sum of column based on partial text and match it with another column

[EitanOrgil]

Hi All,

I have a table of 11 columns and 864 rows.
I wont to sum up column F (has only numbers) if:
last 6 characters in column c (they are numbers) match the number in column K
and
column G is exact text, say, "ABCDE"

so if in raw 1, column c has "texttexttext123456" and in column K I have 123456, and column G has the exact text "ABCDE", add number in column F to sum
Column C may have the same text more then once, same with column K

I tried with: =sumifs(F:F,C:C,RIGHT(C:C,6)=K:K)
I get SPILL error, spill range too big.

will appreciate any help

Eitan

 

[Alex Blakenburg]

I take it from the fact that you are getting a spill error that you have MS 365.
Please update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also you will get a better response if you provide some sample data using XL2BB.

See if this works for you.

=SUM( FILTER(F1:F15, (RIGHT(C1:C15,6)=TRIM(K1:K15)) * (EXACT(G1:G15,"ABCDE")), "") )

 

[EitanOrgil]

I take it from the fact that you are getting a spill error that you have MS 365.
Please update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also you will get a better response if you provide some sample data using XL2BB.

See if this works for you.

=SUM( FILTER(F1:F15, (RIGHT(C1:C15,6)=TRIM(K1:K15)) * (EXACT(G1:G15,"ABCDE")), "") )

Thank you Alex, Updated my profile to MS 365, and OS.
tried your solution, I get the #Value! error, Error in Value.
Attaching XL2BB, first 50 rows:

01.2021-04.2023_RCR_QA_StockMoves.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Date	Product	Reference	Origin	Quantity	Move	Destination	Quantity	Trigram	Job
2	12/04/2021 13:57:24	Product	1.2.3.4:20210309_J121458	Production - controls	0	50	Reception - B	0	PEL	121458
3	12/04/2021 13:57:52	Product	1.2.3.4:20210309_J121458	Reception - B	50	50	Production Room	0	DPE
4	13/04/2021 11:26:39	Product	1.2.3.4:20210309_J121458	Production Room	50	50	Consumption	0	PEL	121554
5	19/04/2021 13:25:49	Product	1.2.3.4:20210309_J121458	Production - controls	-50	50	Reception - B	0	PEL	121458
6	19/04/2021 13:26:19	Product	1.2.3.4:20210309_J121458	Reception - B	50	50	Production Room	0	DPE
7	19/04/2021 13:31:21	Product	1.2.3.4:20210309_J121458	Production Room	50	50	Consumption	50	PEL	121575
8	20/04/2021 14:05:20	Product	1.2.3.4:20210309_J121458	Production - controls	-100	50	Reception - B	0	PEL	121458
9	20/04/2021 14:05:29	Product	1.2.3.4:20210309_J121458	Reception - B	50	50	Production Room	0	DPE
10	20/04/2021 14:06:17	Product	1.2.3.4:20210309_J121458	Production Room	50	50	Consumption	100	PEL	121585
11	21/04/2021 10:26:36	Product	1.2.3.4:20210309_J121458	Production - controls	-150	456	Reception - B	0	PEL	121458
12	21/04/2021 10:26:46	Product	1.2.3.4:20210309_J121458	Reception - B	456	456	Production Room	0	DPE
13	21/04/2021 10:27:55	Product	1.2.3.4:20210309_J121458	Production Room	456	200	Consumption	150	PEL	121593
14	25/04/2021 14:11:00	Product	1.2.3.4:20210309_J121458	Production Room	256	200	Consumption	350	PEL	121608
15	26/04/2021 09:27:08	Product	1.2.3.4:20210309_J121458	Production - controls	-606	944	Reception - B	0	PEL	121458
16	26/04/2021 09:27:51	Product	1.2.3.4:20210309_J121458	Reception - B	944	944	Production Room	56	DPE
17	26/04/2021 09:29:03	Product	1.2.3.4:20210309_J121458	Production Room	1000	1000	Consumption	550	PEL	121614
18	26/04/2021 12:38:20	Product	1.2.3.4:20210309_J121458	Production - controls	-1550	3000	Reception - B	0	PEL	121458
19	26/04/2021 12:38:37	Product	1.2.3.4:20210309_J121458	Reception - B	3000	3000	Production Room	0	DPE
20	26/04/2021 12:41:49	Product	1.2.3.4:20210309_J121458	Production Room	3000	3000	Consumption	1550	PEL	121623
21	26/04/2021 13:37:27	Product	1.2.3.4:20210309_J121458	Production - controls	-4550	21554	Reception - B	0	PEL	121458
22	26/04/2021 13:38:16	Product	1.2.3.4:20210309_J121458	Reception - B	21554	21554	Production Room	0	DPE
23	02/05/2021 09:38:46	Product	1.2.3.4:20210309_J121458	Production Room	21554	8353	Consumption	4550	PEL	121644
24	03/05/2021 10:58:29	Product	1.2.3.4:20210426_J121613	Production - controls	0	27891	Reception	0	PEL	121613
25	03/05/2021 10:58:49	Product	1.2.3.4:20210426_J121613	Reception	27891	27891	Production Room	0	DPE
26	03/05/2021 11:14:45	Product	1.2.3.4:20210309_J121458	Init	0	272	Production Room	13201	DPE
27	05/05/2021 14:14:50	Product	1.2.3.4:20210309_J121458	Consumption	12903	57	02 Machine Failure	0	DPE	121644
28	05/05/2021 14:28:34	Product	1.2.3.4:20210426_J121613	Dummytemppfingaches	0	20	Dummytempgaches	0	DPE	121613
29	05/05/2021 14:28:35	Product	1.2.3.4:20210426_J121613	Dummytempgaches	20	20	05 Testing Gaches	0	DPE	121613
30	05/05/2021 14:28:36	Product	1.2.3.4:20210426_J121613	Dummytemppfingaches	-20	889	Dummytempgaches	0	DPE	121613
31	05/05/2021 14:28:37	Product	1.2.3.4:20210426_J121613	Dummytempgaches	889	889	02 Machine Failure	0	DPE	121613
32	05/05/2021 14:29:00	Product	1.2.3.4:20210309_J121458	Dummytemppfingaches	0	2329	Dummytempgaches	0	DPE	121458
33	05/05/2021 14:29:01	Product	1.2.3.4:20210309_J121458	Dummytempgaches	2329	2329	02 Machine Failure	57	DPE	121458
34	05/05/2021 14:29:02	Product	1.2.3.4:20210309_J121458	Dummytemppfingaches	-2329	20	Dummytempgaches	0	DPE	121458
35	05/05/2021 14:29:03	Product	1.2.3.4:20210309_J121458	Dummytempgaches	20	20	05 Testing Gaches	0	DPE	121458
36	05/05/2021 15:00:06	Product	1.2.3.4:20210309_J121458	Production Room	13473	10	Consumption	12846	PEL	121669
37	05/05/2021 16:50:59	Product	1.2.3.4:20210309_J121458	Production Room	13463	12869	Consumption	12856	PEL	121674
38	09/05/2021 14:11:07	Product	1.2.3.4:20210426_J121613	Production Room	27891	12963	Consumption	0	PEL	121690
39	10/05/2021 12:43:59	Product	1.2.3.4:20210426_J121613	Production Room	14928	12655	Consumption	12963	PEL	121698
40	10/05/2021 13:00:58	Product	1.2.3.4:20210309_J121458	Production Room	594	272	04 Other	0	DPE
41	10/05/2021 15:27:13	Product	1.2.3.4:20210309_J121458	Consumption	25725	16	02 Machine Failure	2386	DPE	121674
42	11/05/2021 15:51:06	Product	1.2.3.4:20210511_J121710	Production - controls	0	9656	Reception	0	PEL	121710
43	11/05/2021 15:51:28	Product	1.2.3.4:20210511_J121710	Reception	9656	9656	Production Room	0	DPE
44	11/05/2021 15:55:30	Product	1.2.3.4:20210511_J121710	Production Room	9656	9656	Consumption	0	PEL	121711
45	12/05/2021 15:39:59	Product	1.2.3.4:20210511_J121710	Production - controls	-9656	12897	Reception	0	PEL	121710
46	12/05/2021 15:40:11	Product	1.2.3.4:20210511_J121710	Reception	12897	12897	Production Room	0	DPE
47	12/05/2021 15:42:05	Product	1.2.3.4:20210511_J121710	Consumption	9656	8	06 Qa Rejects	0	DPE	121711
48	12/05/2021 15:43:12	Product	1.2.3.4:20210511_J121710	Production Room	12897	12897	Consumption	9648	PEL	121719
49	13/05/2021 10:33:40	Product	1.2.3.4:20210511_J121710	Production - controls	-22553	6049	Reception	0	PEL	121710
50	13/05/2021 10:34:06	Product	1.2.3.4:20210511_J121710	Reception	6049	6049	Production Room	0	DPE
Sheet1

 

[Alex Blakenburg]

Try this:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	Product	Reference	Origin	Quantity	Move	Destination	Quantity	Trigram	Job		Text to find -->	Dummytempgaches
2	12/04/2021 13:57	Product	1.2.3.4:20210309_J121458	Production - controls	0	50	Reception - B	0	PEL	121458		Sum with criteria -->	3,258
3	12/04/2021 13:57	Product	1.2.3.4:20210309_J121458	Reception - B	50	50	Production Room	0	DPE
4	13/04/2021 11:26	Product	1.2.3.4:20210309_J121458	Production Room	50	50	Consumption	0	PEL	121554
5	19/04/2021 13:25	Product	1.2.3.4:20210309_J121458	Production - controls	-50	50	Reception - B	0	PEL	121458
6	19/04/2021 13:26	Product	1.2.3.4:20210309_J121458	Reception - B	50	50	Production Room	0	DPE
7	19/04/2021 13:31	Product	1.2.3.4:20210309_J121458	Production Room	50	50	Consumption	50	PEL	121575
8	20/04/2021 14:05	Product	1.2.3.4:20210309_J121458	Production - controls	-100	50	Reception - B	0	PEL	121458
9	20/04/2021 14:05	Product	1.2.3.4:20210309_J121458	Reception - B	50	50	Production Room	0	DPE
10	20/04/2021 14:06	Product	1.2.3.4:20210309_J121458	Production Room	50	50	Consumption	100	PEL	121585
11	21/04/2021 10:26	Product	1.2.3.4:20210309_J121458	Production - controls	-150	456	Reception - B	0	PEL	121458
12	21/04/2021 10:26	Product	1.2.3.4:20210309_J121458	Reception - B	456	456	Production Room	0	DPE
13	21/04/2021 10:27	Product	1.2.3.4:20210309_J121458	Production Room	456	200	Consumption	150	PEL	121593
14	25/04/2021 14:11	Product	1.2.3.4:20210309_J121458	Production Room	256	200	Consumption	350	PEL	121608
15	26/04/2021 9:27	Product	1.2.3.4:20210309_J121458	Production - controls	-606	944	Reception - B	0	PEL	121458
16	26/04/2021 9:27	Product	1.2.3.4:20210309_J121458	Reception - B	944	944	Production Room	56	DPE
17	26/04/2021 9:29	Product	1.2.3.4:20210309_J121458	Production Room	1000	1000	Consumption	550	PEL	121614
18	26/04/2021 12:38	Product	1.2.3.4:20210309_J121458	Production - controls	-1550	3000	Reception - B	0	PEL	121458
19	26/04/2021 12:38	Product	1.2.3.4:20210309_J121458	Reception - B	3000	3000	Production Room	0	DPE
20	26/04/2021 12:41	Product	1.2.3.4:20210309_J121458	Production Room	3000	3000	Consumption	1550	PEL	121623
21	26/04/2021 13:37	Product	1.2.3.4:20210309_J121458	Production - controls	-4550	21554	Reception - B	0	PEL	121458
22	26/04/2021 13:38	Product	1.2.3.4:20210309_J121458	Reception - B	21554	21554	Production Room	0	DPE
23	2/05/2021 9:38	Product	1.2.3.4:20210309_J121458	Production Room	21554	8353	Consumption	4550	PEL	121644
24	3/05/2021 10:58	Product	1.2.3.4:20210426_J121613	Production - controls	0	27891	Reception	0	PEL	121613
25	3/05/2021 10:58	Product	1.2.3.4:20210426_J121613	Reception	27891	27891	Production Room	0	DPE
26	3/05/2021 11:14	Product	1.2.3.4:20210309_J121458	Init	0	272	Production Room	13201	DPE
27	5/05/2021 14:14	Product	1.2.3.4:20210309_J121458	Consumption	12903	57	02 Machine Failure	0	DPE	121644
28	5/05/2021 14:28	Product	1.2.3.4:20210426_J121613	Dummytemppfingaches	0	20	Dummytempgaches	0	DPE	121613
29	5/05/2021 14:28	Product	1.2.3.4:20210426_J121613	Dummytempgaches	20	20	05 Testing Gaches	0	DPE	121613
30	5/05/2021 14:28	Product	1.2.3.4:20210426_J121613	Dummytemppfingaches	-20	889	Dummytempgaches	0	DPE	121613
31	5/05/2021 14:28	Product	1.2.3.4:20210426_J121613	Dummytempgaches	889	889	02 Machine Failure	0	DPE	121613
32	5/05/2021 14:29	Product	1.2.3.4:20210309_J121458	Dummytemppfingaches	0	2329	Dummytempgaches	0	DPE	121458
33	5/05/2021 14:29	Product	1.2.3.4:20210309_J121458	Dummytempgaches	2329	2329	02 Machine Failure	57	DPE	121458
34	5/05/2021 14:29	Product	1.2.3.4:20210309_J121458	Dummytemppfingaches	-2329	20	Dummytempgaches	0	DPE	121458
35	5/05/2021 14:29	Product	1.2.3.4:20210309_J121458	Dummytempgaches	20	20	05 Testing Gaches	0	DPE	121458
36	5/05/2021 15:00	Product	1.2.3.4:20210309_J121458	Production Room	13473	10	Consumption	12846	PEL	121669
37	5/05/2021 16:50	Product	1.2.3.4:20210309_J121458	Production Room	13463	12869	Consumption	12856	PEL	121674
38	9/05/2021 14:11	Product	1.2.3.4:20210426_J121613	Production Room	27891	12963	Consumption	0	PEL	121690
39	10/05/2021 12:43	Product	1.2.3.4:20210426_J121613	Production Room	14928	12655	Consumption	12963	PEL	121698
40	10/05/2021 13:00	Product	1.2.3.4:20210309_J121458	Production Room	594	272	04 Other	0	DPE
41	10/05/2021 15:27	Product	1.2.3.4:20210309_J121458	Consumption	25725	16	02 Machine Failure	2386	DPE	121674
42	11/05/2021 15:51	Product	1.2.3.4:20210511_J121710	Production - controls	0	9656	Reception	0	PEL	121710
43	11/05/2021 15:51	Product	1.2.3.4:20210511_J121710	Reception	9656	9656	Production Room	0	DPE
44	11/05/2021 15:55	Product	1.2.3.4:20210511_J121710	Production Room	9656	9656	Consumption	0	PEL	121711
45	12/05/2021 15:39	Product	1.2.3.4:20210511_J121710	Production - controls	-9656	12897	Reception	0	PEL	121710
46	12/05/2021 15:40	Product	1.2.3.4:20210511_J121710	Reception	12897	12897	Production Room	0	DPE
47	12/05/2021 15:42	Product	1.2.3.4:20210511_J121710	Consumption	9656	8	06 Qa Rejects	0	DPE	121711
48	12/05/2021 15:43	Product	1.2.3.4:20210511_J121710	Production Room	12897	12897	Consumption	9648	PEL	121719
49	13/05/2021 10:33	Product	1.2.3.4:20210511_J121710	Production - controls	-22553	6049	Reception	0	PEL	121710
50	13/05/2021 10:34	Product	1.2.3.4:20210511_J121710	Reception	6049	6049	Production Room	0	DPE
Sheet1

Cell Formulas
Range	Formula
M2	M2	=SUM( FILTER(F2:F50, (RIGHT(C2:C50,6)=TRIM(J2:J50)) * (EXACT(G2:G50,$M$1)), 0) )

 

[EitanOrgil]

Thank you Alex, this works, and I can even select different matches.

Try this:

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	Product	Reference	Origin	Quantity	Move	Destination	Quantity	Trigram	Job		Text to find -->	Dummytempgaches
2	12/04/2021 13:57	Product	1.2.3.4:20210309_J121458	Production - controls	0	50	Reception - B	0	PEL	121458		Sum with criteria -->	3,258
3	12/04/2021 13:57	Product	1.2.3.4:20210309_J121458	Reception - B	50	50	Production Room	0	DPE
4	13/04/2021 11:26	Product	1.2.3.4:20210309_J121458	Production Room	50	50	Consumption	0	PEL	121554
5	19/04/2021 13:25	Product	1.2.3.4:20210309_J121458	Production - controls	-50	50	Reception - B	0	PEL	121458
6	19/04/2021 13:26	Product	1.2.3.4:20210309_J121458	Reception - B	50	50	Production Room	0	DPE
7	19/04/2021 13:31	Product	1.2.3.4:20210309_J121458	Production Room	50	50	Consumption	50	PEL	121575
8	20/04/2021 14:05	Product	1.2.3.4:20210309_J121458	Production - controls	-100	50	Reception - B	0	PEL	121458
9	20/04/2021 14:05	Product	1.2.3.4:20210309_J121458	Reception - B	50	50	Production Room	0	DPE
10	20/04/2021 14:06	Product	1.2.3.4:20210309_J121458	Production Room	50	50	Consumption	100	PEL	121585
11	21/04/2021 10:26	Product	1.2.3.4:20210309_J121458	Production - controls	-150	456	Reception - B	0	PEL	121458
12	21/04/2021 10:26	Product	1.2.3.4:20210309_J121458	Reception - B	456	456	Production Room	0	DPE
13	21/04/2021 10:27	Product	1.2.3.4:20210309_J121458	Production Room	456	200	Consumption	150	PEL	121593
14	25/04/2021 14:11	Product	1.2.3.4:20210309_J121458	Production Room	256	200	Consumption	350	PEL	121608
15	26/04/2021 9:27	Product	1.2.3.4:20210309_J121458	Production - controls	-606	944	Reception - B	0	PEL	121458
16	26/04/2021 9:27	Product	1.2.3.4:20210309_J121458	Reception - B	944	944	Production Room	56	DPE
17	26/04/2021 9:29	Product	1.2.3.4:20210309_J121458	Production Room	1000	1000	Consumption	550	PEL	121614
18	26/04/2021 12:38	Product	1.2.3.4:20210309_J121458	Production - controls	-1550	3000	Reception - B	0	PEL	121458
19	26/04/2021 12:38	Product	1.2.3.4:20210309_J121458	Reception - B	3000	3000	Production Room	0	DPE
20	26/04/2021 12:41	Product	1.2.3.4:20210309_J121458	Production Room	3000	3000	Consumption	1550	PEL	121623
21	26/04/2021 13:37	Product	1.2.3.4:20210309_J121458	Production - controls	-4550	21554	Reception - B	0	PEL	121458
22	26/04/2021 13:38	Product	1.2.3.4:20210309_J121458	Reception - B	21554	21554	Production Room	0	DPE
23	2/05/2021 9:38	Product	1.2.3.4:20210309_J121458	Production Room	21554	8353	Consumption	4550	PEL	121644
24	3/05/2021 10:58	Product	1.2.3.4:20210426_J121613	Production - controls	0	27891	Reception	0	PEL	121613
25	3/05/2021 10:58	Product	1.2.3.4:20210426_J121613	Reception	27891	27891	Production Room	0	DPE
26	3/05/2021 11:14	Product	1.2.3.4:20210309_J121458	Init	0	272	Production Room	13201	DPE
27	5/05/2021 14:14	Product	1.2.3.4:20210309_J121458	Consumption	12903	57	02 Machine Failure	0	DPE	121644
28	5/05/2021 14:28	Product	1.2.3.4:20210426_J121613	Dummytemppfingaches	0	20	Dummytempgaches	0	DPE	121613
29	5/05/2021 14:28	Product	1.2.3.4:20210426_J121613	Dummytempgaches	20	20	05 Testing Gaches	0	DPE	121613
30	5/05/2021 14:28	Product	1.2.3.4:20210426_J121613	Dummytemppfingaches	-20	889	Dummytempgaches	0	DPE	121613
31	5/05/2021 14:28	Product	1.2.3.4:20210426_J121613	Dummytempgaches	889	889	02 Machine Failure	0	DPE	121613
32	5/05/2021 14:29	Product	1.2.3.4:20210309_J121458	Dummytemppfingaches	0	2329	Dummytempgaches	0	DPE	121458
33	5/05/2021 14:29	Product	1.2.3.4:20210309_J121458	Dummytempgaches	2329	2329	02 Machine Failure	57	DPE	121458
34	5/05/2021 14:29	Product	1.2.3.4:20210309_J121458	Dummytemppfingaches	-2329	20	Dummytempgaches	0	DPE	121458
35	5/05/2021 14:29	Product	1.2.3.4:20210309_J121458	Dummytempgaches	20	20	05 Testing Gaches	0	DPE	121458
36	5/05/2021 15:00	Product	1.2.3.4:20210309_J121458	Production Room	13473	10	Consumption	12846	PEL	121669
37	5/05/2021 16:50	Product	1.2.3.4:20210309_J121458	Production Room	13463	12869	Consumption	12856	PEL	121674
38	9/05/2021 14:11	Product	1.2.3.4:20210426_J121613	Production Room	27891	12963	Consumption	0	PEL	121690
39	10/05/2021 12:43	Product	1.2.3.4:20210426_J121613	Production Room	14928	12655	Consumption	12963	PEL	121698
40	10/05/2021 13:00	Product	1.2.3.4:20210309_J121458	Production Room	594	272	04 Other	0	DPE
41	10/05/2021 15:27	Product	1.2.3.4:20210309_J121458	Consumption	25725	16	02 Machine Failure	2386	DPE	121674
42	11/05/2021 15:51	Product	1.2.3.4:20210511_J121710	Production - controls	0	9656	Reception	0	PEL	121710
43	11/05/2021 15:51	Product	1.2.3.4:20210511_J121710	Reception	9656	9656	Production Room	0	DPE
44	11/05/2021 15:55	Product	1.2.3.4:20210511_J121710	Production Room	9656	9656	Consumption	0	PEL	121711
45	12/05/2021 15:39	Product	1.2.3.4:20210511_J121710	Production - controls	-9656	12897	Reception	0	PEL	121710
46	12/05/2021 15:40	Product	1.2.3.4:20210511_J121710	Reception	12897	12897	Production Room	0	DPE
47	12/05/2021 15:42	Product	1.2.3.4:20210511_J121710	Consumption	9656	8	06 Qa Rejects	0	DPE	121711
48	12/05/2021 15:43	Product	1.2.3.4:20210511_J121710	Production Room	12897	12897	Consumption	9648	PEL	121719
49	13/05/2021 10:33	Product	1.2.3.4:20210511_J121710	Production - controls	-22553	6049	Reception	0	PEL	121710
50	13/05/2021 10:34	Product	1.2.3.4:20210511_J121710	Reception	6049	6049	Production Room	0	DPE
Sheet1

Cell Formulas
Range	Formula
M2	M2	=SUM( FILTER(F2:F50, (RIGHT(C2:C50,6)=TRIM(J2:J50)) * (EXACT(G2:G50,$M$1)), 0) )

 

[Alex Blakenburg]

You're welcome. Glad I could help.