Sumif Provided Other Rows = x

[andybason]

Hi all,

I have a table (extract below) and I am using the following formula to sum the values in F where the Q3 criteria is met:

=SUMIF(E2:E77,$Q$3,F2:F77)

I would like to adapt it to only sum modelIDs that have a 1, 2, 3, 4 or 5 in range L2:L77.

Is this possible?

Thanks

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	modelId	Sku	stock	Var1	Var2	C1	C2	C3	C4	C5	order	Rank
2	1034825	1	1	459257	253802	0	0	0	0	0	0	1
3	1034825	1	2	459257	253802	0	0	0	0	0	0	2
4	1034825	1	3	459257	253802	4	0	0	0	0	0	3
5	1034825	1	4	459257	253802	1	0	0	0	0	0	4
6	1034825	1	6	288992	253802	1	0	0	0	1	0	5
7	1034825	1	7	288992	253802	0	0	0	0	0	0	6
8	1034825	1	8	288992	253802	0	0	0	0	0	0	7
9	1034825	1	8	288992	253802	1	0	0	1	0	0	7
10	1034825	1	9	288992	253802	0	0	0	0	0	0	9
11	1034825	1	10	288992	253802	4	0	0	0	0	0	10
12	1034825	1	11	288992	253802	0	0	0	0	0	0	11
13	1034825	1	12	288992	253802	1	0	0	0	0	0	12
14	1034825	1	13	459257	253802	1	0	0	0	0	0	13
15	1034825	1	17	459257	253802	1	0	0	0	0	0	14
16	1034825	1	19	19264	253802	4	0	0	0	0	0	15
17	1034825	1	20	19264	253802	4	0	0	0	0	0	16
18	1034825	1	21	19264	253802	0	0	0	0	0	1	17
19	1034825	1	22	19264	253802	1	0	0	0	0	0	18
20	1034825	1	24	19264	253802	1	0	0	0	0	0	19
21	1034825	1	25	288992	253802	1	0	0	0	0	0	20
22	1034825	1	30	288992	253802	1	0	0	0	0	0	21
23	1034825	1	31	19264	253802	1	0	0	0	0	0	22
24	1034825	1	35	19264	253802	1	0	0	0	0	0	23
25	1034825	1	37	311158	253802	1	0	0	0	0	0	24
26	1034825	1	40	311158	253802	1	0	0	0	0	0	25
27	1034825	1	42	311158	253802	1	0	0	0	0	0	26
28	1034825	1	43	8917	253802	0	0	0	0	0	1	27
29	1023595	1	15	51433	8180	0	0	0	0	0	0	1
30	1023595	1	16	51433	8180	0	0	0	0	0	0	2
31	1023595	1	17	51433	8180	0	0	0	0	0	0	3
32	1023595	1	18	51433	8180	0	0	0	0	0	0	4
33	1023595	1	20	555853	8180	0	0	0	0	0	0	5
34	1023595	1	21	555853	8180	0	0	0	0	0	1	6
35	1023605	2	17	308251	8180	1	0	0	0	0	0	1
36	1023605	2	19	774223	8180	0	0	0	0	0	0	2
37	1023605	2	20	774223	8180	2	0	0	0	0	0	3
38	1023605	2	21	774223	8180	0	0	0	0	0	0	4
39	1023605	2	22	774223	8180	0	0	0	0	0	0	5
40	1023605	2	23	774223	8180	0	0	0	0	0	0	6
41	1023605	2	24	774223	8180	0	0	0	0	0	1	7
42	1023611	2	27	446548	8180	3	0	0	0	0	0	1
43	1023611	2	30	446548	8180	0	0	0	0	0	0	2
44	1023611	2	34	5941	8180	0	0	0	0	0	1	3
45	1023619	2	39	10617	8180	0	0	0	0	0	0	1
46	1023619	2	40	10617	8180	1	0	0	0	0	0	2
47	1023619	2	42	10617	8180	1	0	0	0	0	0	3
48	1023619	2	43	326625	8180	1	0	0	0	0	0	4
49	1023619	2	45	326625	8180	0	0	0	0	0	0	5
50	1023619	2	46	326625	8180	6	0	0	0	0	0	6
51	1023619	2	47	326625	8180	0	0	0	0	0	0	7
52	1023619	2	48	326625	8180	0	0	0	0	0	0	8
53	1023619	2	50	10617	8180	1	0	0	0	0	0	9
54	1023619	2	52	10617	8180	0	0	0	0	0	0	10
55	1023619	2	53	10617	8180	1	0	0	0	0	0	11
56	1023619	2	60	326625	8180	1	0	0	0	0	0	12
57	1023619	2	60	8076	8180	3	0	0	0	3	0	12
58	1023619	2	61	8076	8180	4	0	0	0	0	0	14
59	1023619	2	62	8076	8180	0	0	0	0	0	0	15
60	1023619	2	63	8076	8180	1	0	0	0	0	0	16
61	1023619	2	65	8076	8180	4	0	0	0	0	0	17
62	1023619	2	66	8076	8180	6	0	0	0	0	0	18
63	1023619	2	68	326625	8180	1	0	0	0	0	0	19
64	1023619	2	70	326625	8180	1	0	0	0	0	0	20
65	1023619	2	72	326625	8180	1	0	0	0	0	0	21
66	1023619	2	73	4864	8180	1	0	0	0	0	0	22
67	1023619	2	78	4864	8180	6	0	0	0	0	0	23
68	1023619	2	81	308798	8180	0	0	0	0	0	0	24
69	1023619	2	82	308798	8180	1	0	0	0	0	0	25
70	1023619	2	84	308798	8180	1	0	0	0	0	0	26
71	1023619	2	85	4864	8180	6	0	0	0	0	0	27
72	1023619	2	86	4864	8180	0	0	0	0	0	0	28
73	1023619	2	87	4864	8180	2	0	0	0	0	0	29
74	1023619	2	88	4864	8180	6	0	0	0	0	0	30
75	1023619	2	89	4864	8180	0	0	0	0	0	1	31
76	1023629	2	34	398666	8180	4	0	0	0	0	0	1
77	1023629	2	35	398666	8180	0	0	0	0	0	0	2
Sheet3

 

[Snakehips]

Andy, Not sure if you want 1-5 total or just limited to ID in Q3.
Either way then use SUMIFS

Book1
	M	N	O	P	Q
2	All ID's	19
3	Limited To Q3	6			253802
Sheet5

Cell Formulas
Range	Formula
N2	N2	=SUMIFS(F2:F77,L2:L77,">0",L2:L77,"<6")
N3	N3	=SUMIFS(F2:F77,E2:E77,$Q$3,L2:L77,">0",L2:L77,"<6")

Hope that helps.

 

[andybason]

Hi snakehips,

Sorry, I phrased my request badly...

I would like to sum modelIDs that have a 1, 2, 3, 4 and 5 in range L2:L77.

So, eg, if a modelid had only a 1, 2, 3 and 4 that modelid should not be included in the sum.

Also, it should be limited to Q3. Is this possible?

Thanks

 

[DanteAmor]

It could be with an "auxiliary" column to identify which modelid have 1,2,3,4,5 and then in another cell the sum.

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M	N			Q
1	modelId	Sku	stock	Var1	Var2	C1	C2	C3	C4	C5	order	Rank	Aux	Sum
2	1034825	1	1	459257	253802	0	0	0	0	0	0	1	0	9.00
3	1034825	1	2	459257	253802	0	0	0	0	0	0	2	0				8180
4	1034825	1	3	459257	253802	4	0	0	0	0	0	3	4
5	1034825	1	4	459257	253802	1	0	0	0	0	0	4	1
6	1034825	1	6	288992	253802	1	0	0	0	1	0	5	1
7	1034825	1	7	288992	253802	0	0	0	0	0	0	6	0
8	1034825	1	8	288992	253802	0	0	0	0	0	0	7	0
9	1023595	1	15	51433	8180	0	0	0	0	0	0	1	0
10	1023595	1	16	51433	8180	0	0	0	0	0	0	2	0
11	1023595	1	17	51433	8180	0	0	0	0	0	0	3	0
12	1023595	1	18	51433	8180	0	0	0	0	0	0	4	0
13	1023611	2	27	446548	8180	3	0	0	0	0	0	1	0
14	1023611	2	30	446548	8180	0	0	0	0	0	0	2	0
15	1023611	2	34	5941	8180	0	0	0	0	0	1	3	0
16	1023619	2	39	10617	8180	0	0	0	0	0	0	1	0
17	1023619	2	40	10617	8180	1	0	0	0	0	0	2	1
18	1023619	2	42	10617	8180	1	0	0	0	0	0	3	1
19	1023619	2	43	326625	8180	1	0	0	0	0	0	4	1
20	1023619	2	45	326625	8180	0	0	0	0	0	0	5	0
21	1023619	2	46	326625	8180	6	0	0	0	0	0	6	6
22	1023629	2	34	398666	8180	4	0	0	0	0	0	1	0
23	1023629	2	35	398666	8180	0	0	0	0	0	0	2	0
Sheet3

Cell Formulas
Range	Formula
N2	N2	=SUMIF(E2:E23,Q3,M2:M23)
M2:M23	M2	=IF(SUM(COUNTIFS($A$2:$A$23,A2,$L$2:$L$23,{1,2,3,4,5}))=5,F2,0)

 

[Snakehips]

How's about...

Book1
	N	O	P	Q
3	6			253802
Sheet5

Cell Formulas
Range	Formula
N3	N3	=IF(COUNTIFS(L2:L77,5,E2:E77,$Q$3)>0,(SUMIFS(F2:F77,E2:E77,$Q$3,L2:L77,">0",L2:L77,"<6")),"")

 

[andybason]

Thanks guys, these are great.

 

[DanteAmor]

Glad we could help & thanks for the feedback

 

[andybason]

Hi snakehips,

I have just tried this formula on a larger data set and it still seems to be summing modelIds that have only a 1, 2, 3, or 4. Ideally I would like to avoid a helper column if possible.

Thanks

How's about...

Book1
	N	O	P	Q
3	6			253802
Sheet5

Cell Formulas
Range	Formula
N3	N3	=IF(COUNTIFS(L2:L77,5,E2:E77,$Q$3)>0,(SUMIFS(F2:F77,E2:E77,$Q$3,L2:L77,">0",L2:L77,"<6")),"")

 

[Snakehips]

Hi Andy,

Will my current understanding, I see nothing wrong with this. ???????
Counting only if 1,2,3,4,AND 5

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	modelId	Sku	stock	Var1	Var2	C1	C2	C3	C4	C5	order	Rank
2	1034825	1	1	459257	253802	0	0	0	0	0	0	1
3	1034825	1	2	459257	253802	4	0	0	0	0	0	2		237			253802
4	1034825	1	3	459257	253802	0	0	0	0	0	0	3
5	1034825	1	4	459257	253802	1	0	0	0	0	0	4
6	1034825	1	6	288992	253802	99	0	0	0	1	0	5
7	1034825	1	7	288992	253802	22	0	0	0	0	0	5
8	1034825	1	8	288992	253802	0	0	0	0	0	0	7
9	1034825	1	8	288992	253802	1	0	0	1	0	0	7
10	1034825	1	9	288992	253802	0	0	0	0	0	0	9
11	1034825	1	10	288992	253802	4	0	0	0	0	0	10
12	1034825	1	11	288992	253802	111	0	0	0	0	0	5
13	1034825	1	12	288992	253802	1	0	0	0	0	0	12
Sheet5

Cell Formulas
Range	Formula
N3	N3	=IF(COUNTIFS(L2:L77,5,E2:E77,$Q$3)>0,(SUMIFS(F2:F77,E2:E77,$Q$3,L2:L77,">0",L2:L77,"<6")),"")
L2:L5, L13, L8:L11	L2	=COUNTIFS($A$2:$A$77,A2,$E$2:$E$77,E2,$C$2:$C$77,"<"&C2)+1

Not counting if NOT 5 ??

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	modelId	Sku	stock	Var1	Var2	C1	C2	C3	C4	C5	order	Rank
2	1034825	1	1	459257	253802	0	0	0	0	0	0	1
3	1034825	1	2	459257	253802	4	0	0	0	0	0	2					253802
4	1034825	1	3	459257	253802	0	0	0	0	0	0	3
5	1034825	1	4	459257	253802	1	0	0	0	0	0	4
6	1034825	1	6	288992	253802	99	0	0	0	1	0	7
7	1034825	1	7	288992	253802	22	0	0	0	0	0	7
8	1034825	1	8	288992	253802	0	0	0	0	0	0	7
9	1034825	1	8	288992	253802	1	0	0	1	0	0	7
10	1034825	1	9	288992	253802	0	0	0	0	0	0	9
11	1034825	1	10	288992	253802	4	0	0	0	0	0	10
12	1034825	1	11	288992	253802	111	0	0	0	0	0	10
Sheet5

 

[andybason]

Hi Snakehips,

In the extract below the formula sums the values for ModelID 1034829 but there is only a 1 and a 2 for that ModelID in column L. It should return 6 summing the yellow highlighted cells but also includes the orange highlighted cells.

Thanks

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	ModelId	Sku	stock	Var1	Var2	C1	C2	C3	C4	C5	order	Rank
2	1031665	2	40	315594	308967	0	0	0	0	0	0	1				10
3	1031665	2	41	315594	308967	1	0	0	0	0	0	2					308967
4	1031665	2	48	230558	308967	0	0	0	0	0	0	3
5	1031665	2	51	315594	308967	0	0	0	0	0	0	4
6	1031665	2	52	315594	308967	2	0	0	0	0	0	5
7	1031665	2	53	315594	308967	4	0	0	0	0	0	6
8	1031665	2	54	315594	308967	1	0	0	0	0	0	7
9	1031665	2	55	230558	308967	2	0	0	0	0	0	8
10	1031665	2	56	230558	308967	0	0	0	0	0	0	9
11	1031665	2	57	230558	308967	1	0	0	0	0	0	10
12	1031665	2	59	230558	308967	1	0	0	0	0	0	11
13	1031665	2	64	315594	308967	2	0	0	0	0	0	12
14	1031665	2	65	315594	308967	2	0	0	0	0	0	13
15	1031665	2	66	315594	308967	1	0	0	0	0	0	14
16	1031665	2	67	230559	308967	2	0	0	0	0	0	15
17	1031665	2	68	230559	308967	0	0	0	0	0	0	16
18	1031665	2	69	230559	308967	0	0	0	0	0	0	17
19	1031665	2	70	230559	308967	4	0	0	0	0	0	18
20	1031665	2	71	230559	308967	0	0	0	0	0	0	19
21	1031665	2	72	230559	308967	1	0	0	0	0	0	20
22	1031665	2	73	450075	308967	0	0	0	0	0	0	21
23	1031665	2	74	450075	308967	2	0	0	0	0	0	22
24	1031665	2	75	450075	308967	1	0	0	0	0	0	23
25	1031665	2	76	450075	308967	1	0	0	1	0	0	24
26	1031665	2	77	450075	308967	1	0	0	0	0	0	25
27	1031665	2	81	230559	308967	1	0	0	0	0	0	26
28	1031665	2	83	230559	308967	1	0	0	0	0	0	27
29	1031665	2	85	450075	308967	0	0	0	0	0	1	28
30	1034827	2	102	31820	308967	1	0	0	0	0	0	1
31	1034827	2	103	625383	308967	0	0	0	0	0	0	2
32	1034827	2	104	625383	308967	0	0	0	0	0	0	3
33	1034827	2	105	625383	308967	1	0	0	0	0	0	4
34	1034827	2	109	31820	308967	1	0	0	0	0	0	5
35	1034827	2	112	31820	308967	4	0	0	0	0	0	6
36	1034827	2	113	31820	308967	2	0	0	0	0	0	7
37	1034827	2	114	31820	308967	6	0	0	0	0	0	8
38	1034827	2	117	625383	308967	0	0	0	0	0	0	9
39	1034827	2	118	625383	308967	0	0	0	0	0	1	10
40	1034829	2	85	625383	308967	0	0	0	0	0	0	1
41	1034829	2	86	625383	308967	4	0	0	0	0	1	2
42	1068300	1	39	473004	308967	4	0	0	0	0	0	1
Sheet2

Cell Formulas
Range	Formula
P2	P2	=IF(COUNTIFS(L2:L41,5,E2:E41,$Q$3)>0,(SUMIFS(F2:F41,E2:E41,$Q$3,L2:L41,">0",L2:L41,"<6")),"")