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, I see now that you are only wanting to sum complete sets of 1 to 5

I've looked at this and to be honest, I'm struggling to do it without a helper column.

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		Helper
2	1031665	2	40	315594	308967	0	0	0	0	0	0	1		3
3	1031665	2	41	315594	308967	1	0	0	0	0	0	2				6	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		3
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
Sheet15

Cell Formulas
Range	Formula
P3	P3	=SUM(N2:N42)
N2:N42	N2	=IF(AND(E2=$Q$3,L2=1,SUM(L2:L6)=15),SUM(F2:F6),"")

What is your resistance to a helper, given that you can have it out of the way of your data set / hidden if you wish?
The only other possibility , I reckon, would be using vba to create a UDF.

 

[andybason]

Hi Snakehips,

Thanks for all your help with this. The problem with a helper column is that when the table is refreshed it deletes everything and repastes. This then either deletes additional columns or causes issues with formula references.

 

[Snakehips]

Will you always be applying this to sum F, based on the model on in E and the ranking in L ?
Or, might you use D (Var1) and sum the C2, C3 or C4 columns?

Are you ok with a UDF ?

 

[andybason]

Hi Snakehips,

I won't use Var1 but I may want to sum C2, C3 and C4.

A UDF would be ok.

Thanks

 

[Snakehips]

Andy, give this a try.

Copy the below to a code Module
Parameters:
ModID is the target ModelID Cell Address
DatRng is the whole data range A1: L??
CNum is the numeric suffix of whichever 'C' column, C1, C2, C3, C4 or C5, is to be summed.

It does assume that ALL Rank sequences will begin with 1

Function CSUM(ModID As Range, DatRng As Range, CNum As Integer) As Long

Dim SubTot As Long
Dim DatArry() As Variant
DatArry = DatRng.Value
CNum = CNum + 5
    For r = LBound(DatArry, 1) To UBound(DatArry, 1) - 4
        If DatArry(r, 12) = 1 And DatArry(r, 5) = ModID.Value And DatArry(r + 4, 12) = 5 Then
            For c = r To r + 4
                SubTot = SubTot + DatArry(c, CNum)
            Next c
            r = r + 4
        End If
        CSUM = CSUM + SubTot
        SubTot = 0
    Next r
Set DatArrry = Nothing

End Function

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
3	1031665	2	41	315594	308967	1	0	9	0	0	0	2			C1	6	308967
4	1031665	2	48	230558	308967	0	0	0	0	0	0	3			C3	41
5	1031665	2	51	315594	308967	0	0	13	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	19	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	3	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	625383	308967	4	0	0	0	0	0	1
Sheet15

Cell Formulas
Range	Formula
P3	P3	=CSUM(Q3,A1:L42,1)
P4	P4	=CSUM(Q3,A1:L42,3)

 

[andybason]

Hi Snakehips,

Thanks very much for this. It works well when I tested it on extracts of the full table but when I use it on the full table something seems to go wrong.

I can't attach the workbook to this post so I've uploaded it here: Snakehips Extract.zip

I tested two Var2 numbers: 308967 and 44410. In the first tab are the results of the formula using the full data set. I then extracted the records for each Var2 number and tested the formula against these. It returned different results to the full extract. I went through each extract and manually calculated the expected results and these matched what the formula returned using the extracts.

Sorry to be a pain...

 

[Snakehips]

Andy, I have had a look at the uploaded file.
As it stands, this is not easy. I soon realised that the full data set is not sorted. Hence, unlike the extracted examples you have given, the 1- 5 rank groups for the given Model are not consecutive in the data range. My original UDF assumed that they would be as per the examples. That accounts for the major discrepancy.

Once I realised that,I set about changing the UDF so that it would work on the full, unsorted, data. I had a UDF that I felt should work but for certain ranges, even on the two extracted lists, it returned an error. It took me ages to realise that the rankings are not discrete!!! There are tied ranks!! And, if the tied ranks come within the range 1-5 then based on my current understanding, it causes an issue unless the UDF is geared up to deal with it. Even in the extracted 308967 sheet there are about 7 tied (duplicate) ranks in range 1 - 5.
So, you need to confirm how you need to handle it.
For example, if there is a tied rank 2, so ranks are 1,2,2,4,5,6,7,8 you want the sum 1,2,2,3,4,5. or 1,2,2,3,4. or Ignore such a group all together, and only sum anything 1,2,3,4,5, or what ????????

 

[andybason]

Hi Snakehips,

Sorry, I've been laid low with the flu.

To answer your question, I would like to sum the first 5 including ties, so that would be 1,2,2,3,4 in your example.

Ultimately, what I want to do is sum the first 5, then 2-6, then 3-7, etc. When I posted the original question my intention was to take the logic for 1-5 then replicate it myself for the others.

I really appreciate all your help and no problem if this is too time consuming.

Thanks and keep safe.

 

[Snakehips]

Hi Andy, Sorry you've had the lurgi. Hope you feel and stay better.

If you had 1,2,2,3,4,4,5,... then would your 1 to 5 sum would be 1,2,2,3,4 2 to 6 sum 2,2,3,4,4 ?
Do you compute the ranks or are they downloaded?

I currently have a very tight schedule of staying indoors and twiddling my thumbs but, I will try and find a spare hour or two to see if we can crack this for you.

 

[andybason]

Hi Snakehips,

Thanks. Crazy times.

Yes, the sums would be as you say.

The ranks are imported into Excel from a database.

Thanks