in SUM condition needs to check for a name and its variation. "*" ?

[nahaku]

=SUMIFS(Table4[PCS],Table4[staff],IFS(Table4[staff]=$BP57,$BP57,Table4[staff]="GRS-"&$BP57,"GRS-"&$BP57),Table4[data_source],"GRS")
with this function I will get long array with same numbers, not one number as SUM. It seems like it is just suming all numbers if it is the condition True, not as one :{

I have a column of names where the names are like ABC54646 and sometime it is with GRS-ABC54646, Normaly I would use power query to get rid of it, but I do not want to add aditional tabels as this file will have too many of them already because it will be monthly summary.

Firstly I wanted use just "*" before Cell reference but It then cant look up somethink like ....ABC54646 it would look up "*$BP7" and of course it fails....
Any suggestions?

 

[Peter_SSs]

Any chance of a small set of sample data and expected results with XL2BB?
Giving us no data or results no layout and a formula that does not do what you want it to, doesn't make it easy for us to replicate what you have or want.

 

[nahaku]

Any chance of a small set of sample data and expected results with XL2BB?
Giving us no data or results no layout and a formula that does not do what you want it to, doesn't make it easy for us to replicate what you have or want.

Test of Packing KPI monthly Test.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	staff	times	PCS	weight	volume
2	staff	times	PCS	weight	volume			Putaway	Time on different	GRS					Targ/H
3	GRS-ABC0156	0	6	37.02	0.584496					Time [H]	Pcs	Weight	Volume	PCs / Hour	700
4	GRS-ABC0156	20	1	2	0.02457			ABC0156	0	0.00	0	0.00	0.00
5	GRS-ABC0156	157	1	3.87	0.02312			ABC0347	0	0.00	0	0.00	0.00
6	GRS-ABC0156	19	1	1.97	0.006732			ABC0157	0	0.00	0	0.00	0.00
7	GRS-ABC0156	21	1	3.32	0.023936			ABC0209	0	0.00	0	0.00	0.00
8	GRS-ABC0156	23	1	3.1	0.02013				0	1.03	47	213.59	2.24	45.55	6.5%
9	GRS-ABC0156	126	1	4	0.018444				0	1.03	47	213.59	2.24	45.55	6.5%
10	GRS-ABC0156	26	1	3.98	0.020163				0	1.03	47	213.59	2.24	45.55	6.5%
11	GRS-ABC0156	23	1	3.35	0.04959				0	1.03	47	213.59	2.24	45.55	6.5%
12	GRS-ABC0156	23	1	3.35	0.04959				0	1.03	47	213.59	2.24	45.55	6.5%
13	GRS-ABC0156	598	2	1.52	0.02125
14	GRS-ABC0156	28	2	1.66	0.0084
15	GRS-ABC0156	23	1	2.3	0.01248
16	GRS-ABC0156	21	2	1	0.003888
17	GRS-ABC0156	29	1	1.69	0.0074
18	GRS-ABC0156	29	2	3.76	0.010962
19	GRS-ABC0156	59	1	13	0.078052
20	GRS-ABC0156	24	1	1.8	0.025308
21	GRS-ABC0156	254	1	2.03	0.05742
22	GRS-ABC0156	22	1	0.3	0.001408
23	GRS-ABC0156	31	1	4.36	0.01624
24	GRS-ABC0156	117	1	4.46	0.123525
25	GRS-ABC0156	191	1	5.39	0.075696
26	GRS-ABC0156	26	1	0.211	0.00156
27	GRS-ABC0156	16	1	0.267	0.00112
28	GRS-ABC0156	15	1	0.65	0.009072
29	GRS-ABC0156	190	1	0.97	0.00966
30	GRS-ABC0156	86	1	1.836	0.01728
31	GRS-ABC0156	942	1	1.7	0.016105
32	GRS-ABC0156	122	1	1.7	0.016105
33	GRS-ABC0156	31	1	7.1	0.04508
34	GRS-ABC0156	212	1	1.7	0.016105
35	GRS-ABC0156	42	1	1.64	0.01728
36	GRS-ABC0156	88	1	1.64	0.01728
37	GRS-ABC0156	54	1	0.07	0.000413
38	GRS-ABC0156	21	1	2.58	0.0054
39	ABC0157	11	1	28.1	0.2754
40	ABC0157	15	1	28.1	0.2754
41	ABC0157	0	1	28.1	0.2754
42	ABC0209	0	60	63	0.86688
43	ABC0209	479	60	456	1.65957
44	ABC0209	28	60	133.2	0.41496
45	ABC0209	336	60	196.44	0.814275
46	ABC0209	0	96	19.2	0.058205
47	ABC0209	97	96	119.424	0.576576
Sheet1

Cell Formulas
Range	Formula
J4:J12	J4	=SUMIFS(Table2[Destroy Pick],Table2[Login],$I4)
K4:K12	K4	=SUMIFS(Table4[times],Table4[staff],"*"&$I4,Table4[data_source],"GRS")/3600
L4:L12	L4	=SUMIFS(Table4[PCS],Table4[staff],"*"&$I4,Table4[data_source],"GRS")
M4:M12	M4	=SUMIFS(Table4[weight],Table4[staff],"*"&$I4,Table4[data_source],"GRS")
N4:N12	N4	=SUMIFS(Table4[volume],Table4[staff],"*"&$I4,Table4[data_source],"GRS")
O4:O12	O4	=IFERROR(L4/K4,"")
P4:P12	P4	=IFERROR((O4/$P$3),"")

 

[Peter_SSs]

Thanks, but that has raised some more questions.

I asked for the expected results. Did you provide them?
If so, where in that sheet are they?
If not, please provide them as well as an explanation of how to get them manually.

You have shown a formula that includes Table2[Destroy Pick]
I presume that means that I cannot see Table2? Do I need to know about Table2?

I can see 'times', 'staff', 'PCS' etc columns in the left hand table, but not 'data_source'. Does that mean the left hand table is not Table4 or that 'data_source' is perhaps not shown (cloumn A?)? Do I need to know about 'data_source'?

Remember that you are very familiar with your sheet, data, layout & what you want. We have no idea unless you spell it out very clearly.

 

[nahaku]

Thanks, but that has raised some more questions.

I asked for the expected results. Did you provide them?
If so, where in that sheet are they?
If not, please provide them as well as an explanation of how to get them manually.

You have shown a formula that includes Table2[Destroy Pick]
I presume that means that I cannot see Table2? Do I need to know about Table2?

I can see 'times', 'staff', 'PCS' etc columns in the left hand table, but not 'data_source'. Does that mean the left hand table is not Table4 or that 'data_source' is perhaps not shown (cloumn A?)? Do I need to know about 'data_source'?

Remember that you are very familiar with your sheet, data, layout & what you want. We have no idea unless you spell it out very clearly.

the other tab is different, there I have no problems, but I have problems to sum rows for ABC0156 when in this tab I post here is instead of ABC0156 is GRS-ABC0156 . =SUMIFS(Table4[PCS],Table4[staff],"*"&$I4,Table4[data_source],"GRS") => [staff] is column B and datasouse would be Column A if is here, there are value only GRS and Other... and I need to sum values in column D what is equivalent of [PCS] . Criterium is in Column "i" what it should be looking for, as you can see when i use as criteria "*"&$I4 it is only counting something wrong, probably empty cells... weight and volum sum would be just substitution of [PCS] for the column i need to sum.

 

[Peter_SSs]

Again you have given a formula that does not work, no expected results and sample data with a column missing. Hard to help when you don't have all the data required or know what result should be returned.

What I can say is that if I add a data_source column to that left table and enter "GRS" in all rows then the PCS formula on the right returns 45 for ABC0156.

 

[nahaku]

Again you have given a formula that does not work, no expected results and sample data with a column missing. Hard to help when you don't have all the data required or know what result should be returned.

What I can say is that if I add a data_source column to that left table and enter "GRS" in all rows then the PCS formula on the right returns 45 for ABC0156.

I thought I was clear, that I need to SUM those cells where in row is Name with prefix "GRS-" If you missunderstand me, I am sorry, English is not my language.

Test of Packing KPI monthly Test.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1								How it should look
2	staff	times	PCS	weight	volume	data_source		Putaway	Time on Diff dep	GRS
3	staff	times	PCS	weight	volume	data_source				Time	Pcs	Weight	Volume	PCs / Hour
4	GRS-ABC0156	0	6	37.02	0.584496	GRS		ABC0157	0	26.00	3	84.30	0.83	415.38
5	GRS-ABC0156	20	1	2	0.02457	GRS		ABC0087	0	0.00	0	0.00	0.00
6	GRS-ABC0156	157	1	3.87	0.02312	GRS		ABC0209	0	0.00	0	0.00	0.00
7	GRS-ABC0156	19	1	1.97	0.006732	GRS		ABC0156	0	3709.00	45	131.29	1.44	43.68
8	GRS-ABC0156	21	1	3.32	0.023936	GRS
9	GRS-ABC0156	23	1	3.1	0.02013	GRS
10	GRS-ABC0156	126	1	4	0.018444	GRS
11	GRS-ABC0156	26	1	3.98	0.020163	GRS
12	GRS-ABC0156	23	1	3.35	0.04959	GRS
13	GRS-ABC0156	23	1	3.35	0.04959	GRS			what I usually use…. Down here
14	GRS-ABC0156	598	2	1.52	0.02125	GRS				Time	Pcs	Weight	Volume
15	GRS-ABC0156	28	2	1.66	0.0084	GRS			ABC0157	26	3	84.3	0.8262
16	GRS-ABC0156	23	1	2.3	0.01248	GRS			ABC0087	0	0	0	0
17	GRS-ABC0156	21	2	1	0.003888	GRS			ABC0209	0	0	0	0
18	GRS-ABC0156	29	1	1.69	0.0074	GRS			ABC0156	0	0	0	0
19	GRS-ABC0156	29	2	3.76	0.010962	GRS				0	0	0	0
20	GRS-ABC0156	59	1	13	0.078052	GRS				0	0	0	0
21	GRS-ABC0156	24	1	1.8	0.025308	GRS				0	0	0	0
22	GRS-ABC0156	254	1	2.03	0.05742	GRS
23	GRS-ABC0156	22	1	0.3	0.001408	GRS
24	GRS-ABC0156	31	1	4.36	0.01624	GRS
25	GRS-ABC0156	117	1	4.46	0.123525	GRS
26	GRS-ABC0156	191	1	5.39	0.075696	GRS
27	GRS-ABC0156	26	1	0.211	0.00156	GRS
28	GRS-ABC0156	16	1	0.267	0.00112	GRS
29	GRS-ABC0156	15	1	0.65	0.009072	GRS
30	GRS-ABC0156	190	1	0.97	0.00966	GRS
31	GRS-ABC0156	86	1	1.836	0.01728	GRS
32	GRS-ABC0156	942	1	1.7	0.016105	GRS
33	GRS-ABC0156	122	1	1.7	0.016105	GRS
34	GRS-ABC0156	31	1	7.1	0.04508	GRS
35	GRS-ABC0156	212	1	1.7	0.016105	GRS
36	GRS-ABC0156	42	1	1.64	0.01728	GRS
37	GRS-ABC0156	88	1	1.64	0.01728	GRS
38	GRS-ABC0156	54	1	0.07	0.000413	GRS
39	GRS-ABC0156	21	1	2.58	0.0054	GRS
40	ABC0157	11	1	28.1	0.2754	GRS
41	ABC0157	15	1	28.1	0.2754	GRS
42	ABC0157	0	1	28.1	0.2754	GRS
43	ABC0209	0	60	63	0.86688	XCF
44	ABC0209	479	60	456	1.65957	XCF
45	ABC0209	28	60	133.2	0.41496	XCF
46	ABC0209	336	60	196.44	0.814275	XCF
47	ABC0209	0	96	19.2	0.058205	XCF
48	ABC0209	97	96	119.424	0.576576	XCF
Sheet1

Cell Formulas
Range	Formula
O4:O12	O4	=IFERROR(L4/(K4/3600),"")
K15:K21	K15	=SUMIFS(C3:C48,B3:B48,J15,G3:G48,"GRS")
L15:L21	L15	=SUMIFS(D3:D48,B3:B48,J15,G3:G48,"GRS")
M15:M21	M15	=SUMIFS(E3:E48,B3:B48,J15,G3:G48,"GRS")
N15:N21	N15	=SUMIFS(F3:F48,B3:B48,J15,G3:G48,"GRS")

 

[Peter_SSs]

If you missunderstand me, I am sorry, English is not my language.

Exactly why I wanted the expected results as there is no language issue then.


As far as I can see, formulas of the form you showed in post #3 produce those expected results. Here is my sheet with those formulas (slightly adjusted for different row numbers)

BTW, why does the table in columns B:G have two identical header rows?

nahaku 2020-04-18 2.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2								How it should look
3	staff	times	PCS	weight	volume	data_source		Putaway	Time on Diff dep	GRS
4	staff	times	PCS	weight	volume	data_source				Time	Pcs	Weight	Volume	PCs / Hour
5	GRS-ABC0156	0	6	37.02	0.584496	GRS		ABC0157	0	26	3	84.3	0.8262	415.3846154
6	GRS-ABC0156	20	1	2	0.02457	GRS		ABC0087	0	0	0	0	0
7	GRS-ABC0156	157	1	3.87	0.02312	GRS		ABC0209	0	0	0	0	0
8	GRS-ABC0156	19	1	1.97	0.006732	GRS		ABC0156	0	3709	45	131.294	1.43526	43.67754112
9	GRS-ABC0156	21	1	3.32	0.023936	GRS
10	GRS-ABC0156	23	1	3.1	0.02013	GRS
11	GRS-ABC0156	126	1	4	0.018444	GRS
12	GRS-ABC0156	26	1	3.98	0.020163	GRS
13	GRS-ABC0156	23	1	3.35	0.04959	GRS
14	GRS-ABC0156	23	1	3.35	0.04959	GRS
15	GRS-ABC0156	598	2	1.52	0.02125	GRS
16	GRS-ABC0156	28	2	1.66	0.0084	GRS
17	GRS-ABC0156	23	1	2.3	0.01248	GRS
18	GRS-ABC0156	21	2	1	0.003888	GRS
19	GRS-ABC0156	29	1	1.69	0.0074	GRS
20	GRS-ABC0156	29	2	3.76	0.010962	GRS
21	GRS-ABC0156	59	1	13	0.078052	GRS
22	GRS-ABC0156	24	1	1.8	0.025308	GRS
23	GRS-ABC0156	254	1	2.03	0.05742	GRS
24	GRS-ABC0156	22	1	0.3	0.001408	GRS
25	GRS-ABC0156	31	1	4.36	0.01624	GRS
26	GRS-ABC0156	117	1	4.46	0.123525	GRS
27	GRS-ABC0156	191	1	5.39	0.075696	GRS
28	GRS-ABC0156	26	1	0.211	0.00156	GRS
29	GRS-ABC0156	16	1	0.267	0.00112	GRS
30	GRS-ABC0156	15	1	0.65	0.009072	GRS
31	GRS-ABC0156	190	1	0.97	0.00966	GRS
32	GRS-ABC0156	86	1	1.836	0.01728	GRS
33	GRS-ABC0156	942	1	1.7	0.016105	GRS
34	GRS-ABC0156	122	1	1.7	0.016105	GRS
35	GRS-ABC0156	31	1	7.1	0.04508	GRS
36	GRS-ABC0156	212	1	1.7	0.016105	GRS
37	GRS-ABC0156	42	1	1.64	0.01728	GRS
38	GRS-ABC0156	88	1	1.64	0.01728	GRS
39	GRS-ABC0156	54	1	0.07	0.000413	GRS
40	GRS-ABC0156	21	1	2.58	0.0054	GRS
41	ABC0157	11	1	28.1	0.2754	GRS
42	ABC0157	15	1	28.1	0.2754	GRS
43	ABC0157	0	1	28.1	0.2754	GRS
44	ABC0209	0	60	63	0.86688	XCF
45	ABC0209	479	60	456	1.65957	XCF
46	ABC0209	28	60	133.2	0.41496	XCF
47	ABC0209	336	60	196.44	0.814275	XCF
48	ABC0209	0	96	19.2	0.058205	XCF
49	ABC0209	97	96	119.424	0.576576	XCF
Sheet1

Cell Formulas
Range	Formula
K5:K8	K5	=SUMIFS(Table4[times],Table4[staff],"*"&$I5,Table4[data_source],"GRS")
L5:L8	L5	=SUMIFS(Table4[PCS],Table4[staff],"*"&$I5,Table4[data_source],"GRS")
M5:M8	M5	=SUMIFS(Table4[weight],Table4[staff],"*"&$I5,Table4[data_source],"GRS")
N5:N8	N5	=SUMIFS(Table4[volume],Table4[staff],"*"&$I5,Table4[data_source],"GRS")
O5:O8	O5	=IFERROR(L5/(K5/3600),"")

 

[nahaku]

Exactly why I wanted the expected results as there is no language issue then.


As far as I can see, formulas of the form you showed in post #3 produce those expected results. Here is my sheet with those formulas (slightly adjusted for different row numbers)

BTW, why does the table in columns B:G have two identical header rows?
[/RANGE]

It has that row only because it was copy paste data format in to the pre-created Table what was made earlier, and is easier to do ctrl+a copy paste then select those data :P

now with this formula =SUMIFS(Table4[times],Table4[staff],"*"&$I5,Table4[data_source],"GRS")
there is issue when the i5 cell is empty it will shows some value.... is not there some way how to use in this function OR? I tried IF but it will brings results as array with split error.
When i use instead o f "*" "GRS-" then it will not show values for names where there is no GRS- prefix.
Is there any substitute for OR in this formula same as & is for AND ?
when i use =SUMIFS(Table4[times],Table4[staff],"*"&$I5,Table4[data_source],"GRS",I5,"<>" ) it shows Value error

 

[Peter_SSs]

now with this formula =SUMIFS(Table4[times],Table4[staff],"*"&$I5,Table4[data_source],"GRS")
there is issue when the i5 cell is empty it will shows some value.

Couldn't you just check for an empty cell first like this?

nahaku 2020-04-18 2.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2								How it should look
3	staff	times	PCS	weight	volume	data_source		Putaway	Time on Diff dep	GRS
4	staff	times	PCS	weight	volume	data_source				Time	Pcs	Weight	Volume	PCs / Hour
5	GRS-ABC0156	0	6	37.02	0.584496	GRS			0
6	GRS-ABC0156	20	1	2	0.02457	GRS		ABC0087	0	0	0	0	0
7	GRS-ABC0156	157	1	3.87	0.02312	GRS		ABC0209	0	0	0	0	0
8	GRS-ABC0156	19	1	1.97	0.006732	GRS
9	GRS-ABC0156	21	1	3.32	0.023936	GRS		ABC0156	0	3709	45	131.294	1.43526	43.67754112
10	GRS-ABC0156	23	1	3.1	0.02013	GRS
11	GRS-ABC0156	126	1	4	0.018444	GRS
12	GRS-ABC0156	26	1	3.98	0.020163	GRS
13	GRS-ABC0156	23	1	3.35	0.04959	GRS
14	GRS-ABC0156	23	1	3.35	0.04959	GRS
15	GRS-ABC0156	598	2	1.52	0.02125	GRS
16	GRS-ABC0156	28	2	1.66	0.0084	GRS
17	GRS-ABC0156	23	1	2.3	0.01248	GRS
18	GRS-ABC0156	21	2	1	0.003888	GRS
19	GRS-ABC0156	29	1	1.69	0.0074	GRS
20	GRS-ABC0156	29	2	3.76	0.010962	GRS
21	GRS-ABC0156	59	1	13	0.078052	GRS
22	GRS-ABC0156	24	1	1.8	0.025308	GRS
23	GRS-ABC0156	254	1	2.03	0.05742	GRS
24	GRS-ABC0156	22	1	0.3	0.001408	GRS
25	GRS-ABC0156	31	1	4.36	0.01624	GRS
26	GRS-ABC0156	117	1	4.46	0.123525	GRS
27	GRS-ABC0156	191	1	5.39	0.075696	GRS
28	GRS-ABC0156	26	1	0.211	0.00156	GRS
29	GRS-ABC0156	16	1	0.267	0.00112	GRS
30	GRS-ABC0156	15	1	0.65	0.009072	GRS
31	GRS-ABC0156	190	1	0.97	0.00966	GRS
32	GRS-ABC0156	86	1	1.836	0.01728	GRS
33	GRS-ABC0156	942	1	1.7	0.016105	GRS
34	GRS-ABC0156	122	1	1.7	0.016105	GRS
35	GRS-ABC0156	31	1	7.1	0.04508	GRS
36	GRS-ABC0156	212	1	1.7	0.016105	GRS
37	GRS-ABC0156	42	1	1.64	0.01728	GRS
38	GRS-ABC0156	88	1	1.64	0.01728	GRS
39	GRS-ABC0156	54	1	0.07	0.000413	GRS
40	GRS-ABC0156	21	1	2.58	0.0054	GRS
41	ABC0157	11	1	28.1	0.2754	GRS
42	ABC0157	15	1	28.1	0.2754	GRS
43	ABC0157	0	1	28.1	0.2754	GRS
44	ABC0209	0	60	63	0.86688	XCF
45	ABC0209	479	60	456	1.65957	XCF
46	ABC0209	28	60	133.2	0.41496	XCF
47	ABC0209	336	60	196.44	0.814275	XCF
48	ABC0209	0	96	19.2	0.058205	XCF
49	ABC0209	97	96	119.424	0.576576	XCF
Sheet1

Cell Formulas
Range	Formula
K5:K9	K5	=IF(I5="","",SUMIFS(Table4[times],Table4[staff],"*"&$I5,Table4[data_source],"GRS"))
L5:L9	L5	=IF(I5="","",SUMIFS(Table4[PCS],Table4[staff],"*"&$I5,Table4[data_source],"GRS"))
M5:M9	M5	=IF(I5="","",SUMIFS(Table4[weight],Table4[staff],"*"&$I5,Table4[data_source],"GRS"))
N5:N9	N5	=IF(I5="","",SUMIFS(Table4[volume],Table4[staff],"*"&$I5,Table4[data_source],"GRS"))
O5:O9	O5	=IFERROR(L5/(K5/3600),"")