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]

Just about to take a look at this but immediately unsure
I need to identify the start of each set of ranks. Given by rank 1.
However, how can do that if there is the possibility of ties for rank 1 ?
If the succession of ranks for a model went 1,1,1,2,3,4,5,6.... how would we be able to determine whether that's
Three tied rank 1 as per 1,1,1,2,3 or a single returned rank 1 followed by a separate sequence starting with two tied 1's etc ????

 

[Snakehips]

@radeon187 I tihnk I've answered my own question.
Var2 is the primary lookup
Each set of ranks, is associated with a different, discrete model ID ?
If so then then tied rank 1 are not an issue

 

[andybason]

Hi Snakehips, thanks. Let me have a think when my head is less full of cotton wool.

 

[Snakehips]

This has driven me round the bend! I have been getting discrepancies and assuming errors in the function logic. Going round and round in circles and never seeing nor fixing it. Then I realise that your 308967 extract data is not a true match for the apparent equivalent in the full data set.

Hopefully the below function does the following.
Sums 5 consecutive Rank rows, if there are 5, ignoring the actual rank numbers where they duplicate for ties. So 1,2,3,4,5. or 1,2,2,4,5
Parameters are:
Var , the range holding the relevant Var2 number eg Q3
DatRng , the range of the data table
CNUm , the relevant Suffix for C1, C2, C3 etc. eg 1 , 2, 3
RRt , the root number for 5 ranks to be summed. eg 1. for 1,2,3,4,5 , 2 for 2,3,4,5,6. etc

Function CSUM(Var As Range, DatRng As Range, CNum As Integer, RRt As Integer) As Long

Dim ModID As Variant
Dim SubTot As Long
Dim DatArry() As Variant
Dim c As Integer
DatArry = DatRng.Value  'Data range to array

CNum = CNum + 5 'set column for C1, C2, etc
c = 0
    For r = LBound(DatArry, 1) To UBound(DatArry, 1)
            If DatArry(r, 5) = Var And DatArry(r, 12) <= RRt + 4 Then  'have a look
            
                    If DatArry(r, 12) = 1 And c = 0 Then  'is ist rank 1
                        ModID = DatArry(r, 1) 'Note the Model
                        c = 1  'Inc c count
                        If RRt = 1 Then SubTot = DatArry(r, CNum)  'Sub total
                        GoTo Done
                    End If
                 
                    If DatArry(r, 1) = ModID Then  'Not first so check Model same
                        c = c + 1  'Inc c count
                        If c >= RRt And c < (RRt + 5) Then SubTot = SubTot + DatArry(r, CNum)  'Update sub tot
                    Else  'otherwise if not same Model it is start of new set
                        ModID = DatArry(r, 1) 'Note the new Model
                        c = 1
                        If RRt = 1 Then SubTot = DatArry(r, CNum)  'Update sub tot
                     
                    End If
                  
                    If c = RRt + 4 Then
                        CSUM = CSUM + SubTot
                        SubTot = 0
                    End If
            End If
Done:
     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	936153	1	1	49764	280490	1	0	0	0	0	0	1				C1 1 to 5	Var2 Lookup
3	936153	1	2	49764	45955	4	0	0	0	0	0	1				481	308967
4	936153	1	3	49764	45955	1	0	0	0	0	0	2
5	936153	1	4	49764	280490	1	0	0	0	0	0	2
6	936153	1	5	49764	45955	1	0	0	0	0	0	3
7	936153	1	5	49764	280490	1	0	0	1	0	0	3
Full Data Set

Cell Formulas
Range	Formula
P3	P3	=CSUM(Q3,A1:L481500,1,1)

With nearly 5000,000 rows, It won't be the fastest formula to calculate but isn't too bad.
Check it out carefully.

 

[andybason]

Hi Snakehips,

Apologies for the delay. This is great. Thank you again for your help