Vlookup/Sumif/or CSE formula quandry?

RickM · Mar 8, 2011

I am trying to return sum of quarterly data to another cell. Looking for a formula to return the sum of 11175 colC (120+117+234) in another cell and then (261+253+307) etc. Will need that for each ColumnC thru ColumnI. The Date will change each month. Next month the first date will be Feb/2010. So I am trying to get the first three cells in ColC and the next 3 cells in ColC. Not sure which formula to use??????

Excel Workbook

A

B

C

D

E

F

G

H

I

2

Branch #

Date

A

B

C

D

E

F

N

3

11175

Jan/2010

120

10

19

18

10

38

70

4

11175

Feb/2010

117

11

23

17

10

75

70

5

11175

Mar/2010

234

22

14

20

10

90

141

6

11175

Apr/2010

261

34

30

27

10

86

137

7

11175

May/2010

253

36

19

23

10

59

116

8

11175

Jun/2010

307

35

51

25

12

83

142

9

11175

Jul/2010

298

24

42

25

10

42

110

10

11175

Aug/2010

219

19

15

19

18

54

111

11

11175

Sep/2010

85

16

15

8

7

27

63

12

11175

Oct/2010

65

6

1

2

3

15

42

13

11175

Nov/2010

86

1

14

9

2

27

58

14

11175

Dec/2010

223

25

32

12

85

152

15

11175

Jan/2011

235

30

26

18

10

77

125

16

11200

Jan/2010

26

2

1

14

17

11200

Feb/2010

37

1

2

1

2

14

18

11200

Mar/2010

41

2

1

21

19

11200

Apr/2010

28

2

1

2

15

20

11200

May/2010

33

2

3

1

6

14

21

11200

Jun/2010

34

1

2

1

33

22

11200

Jul/2010

29

3

2

1

3

16

23

11200

Aug/2010

51

4

1

3

24

11200

Sep/2010

45

2

1

7

37

25

11200

Oct/2010

54

3

2

3

7

49

26

11200

Nov/2010

44

3

2

3

22

27

11200

Dec/2010

35

2

24

28

11200

Jan/2011

37

2

4

3

15

DATA

Excel 2007

Gerald Higgins · Mar 8, 2011

I think it's this (I say think, I've just closed Excel down as I'm logging off now, I'll look back tomorrow)...

Code:

=SUM(OFFSET(A3,MATCH(K4,A3:A28,0)-1,3,3,1))

Haseeb Avarakkan · Mar 8, 2011

Try,

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Branch #</td><td style=";">Date</td><td style=";">A</td><td style=";">B</td><td style=";">C</td><td style=";">D</td><td style=";">E</td><td style=";">F</td><td style=";">N</td><td style="text-align: right;;"></td><td style=";">Lookup Branch</td><td style="text-align: right;;">11175</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">11175</td><td style="text-align: right;;">1-Jan-10</td><td style="text-align: right;;">120</td><td style="text-align: right;;">10</td><td style="text-align: right;;">19</td><td style="text-align: right;;">18</td><td style="text-align: right;;">10</td><td style="text-align: right;;">38</td><td style="text-align: right;;">70</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2010</td><td style="text-align: right;;">2011</td><td style="text-align: right;;">2012</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">11175</td><td style="text-align: right;;">1-Feb-10</td><td style="text-align: right;;">117</td><td style="text-align: right;;">11</td><td style="text-align: right;;">23</td><td style="text-align: right;;">17</td><td style="text-align: right;;">10</td><td style="text-align: right;;">75</td><td style="text-align: right;;">70</td><td style="text-align: right;;"></td><td style=";">Qtr1</td><td style="text-align: right;;">1139</td><td style="text-align: right;;">521</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">11175</td><td style="text-align: right;;">1-Mar-10</td><td style="text-align: right;;">234</td><td style="text-align: right;;">22</td><td style="text-align: right;;">14</td><td style="text-align: right;;">20</td><td style="text-align: right;;">10</td><td style="text-align: right;;">90</td><td style="text-align: right;;">141</td><td style="text-align: right;;"></td><td style=";">Qtr2</td><td style="text-align: right;;">1756</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">11175</td><td style="text-align: right;;">1-Apr-10</td><td style="text-align: right;;">261</td><td style="text-align: right;;">34</td><td style="text-align: right;;">30</td><td style="text-align: right;;">27</td><td style="text-align: right;;">10</td><td style="text-align: right;;">86</td><td style="text-align: right;;">137</td><td style="text-align: right;;"></td><td style=";">Qtr3</td><td style="text-align: right;;">1227</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">11175</td><td style="text-align: right;;">1-May-10</td><td style="text-align: right;;">253</td><td style="text-align: right;;">36</td><td style="text-align: right;;">19</td><td style="text-align: right;;">23</td><td style="text-align: right;;">10</td><td style="text-align: right;;">59</td><td style="text-align: right;;">116</td><td style="text-align: right;;"></td><td style=";">Qtr4</td><td style="text-align: right;;">885</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">11175</td><td style="text-align: right;;">1-Jun-10</td><td style="text-align: right;;">307</td><td style="text-align: right;;">35</td><td style="text-align: right;;">51</td><td style="text-align: right;;">25</td><td style="text-align: right;;">12</td><td style="text-align: right;;">83</td><td style="text-align: right;;">142</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">11175</td><td style="text-align: right;;">1-Jul-10</td><td style="text-align: right;;">298</td><td style="text-align: right;;">24</td><td style="text-align: right;;">42</td><td style="text-align: right;;">25</td><td style="text-align: right;;">10</td><td style="text-align: right;;">42</td><td style="text-align: right;;">110</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">11175</td><td style="text-align: right;;">1-Aug-10</td><td style="text-align: right;;">219</td><td style="text-align: right;;">19</td><td style="text-align: right;;">15</td><td style="text-align: right;;">19</td><td style="text-align: right;;">18</td><td style="text-align: right;;">54</td><td style="text-align: right;;">111</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">11175</td><td style="text-align: right;;">1-Sep-10</td><td style="text-align: right;;">85</td><td style="text-align: right;;">16</td><td style="text-align: right;;">15</td><td style="text-align: right;;">8</td><td style="text-align: right;;">7</td><td style="text-align: right;;">27</td><td style="text-align: right;;">63</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">11175</td><td style="text-align: right;;">1-Oct-10</td><td style="text-align: right;;">65</td><td style="text-align: right;;">6</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">15</td><td style="text-align: right;;">42</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">11175</td><td style="text-align: right;;">1-Nov-10</td><td style="text-align: right;;">86</td><td style="text-align: right;;">1</td><td style="text-align: right;;">14</td><td style="text-align: right;;">9</td><td style="text-align: right;;">2</td><td style="text-align: right;;">27</td><td style="text-align: right;;">58</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">11175</td><td style="text-align: right;;">1-Dec-10</td><td style="text-align: right;;">223</td><td style="text-align: right;;">25</td><td style="text-align: right;;">25</td><td style="text-align: right;;">32</td><td style="text-align: right;;">12</td><td style="text-align: right;;">85</td><td style="text-align: right;;">152</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">11175</td><td style="text-align: right;;">1-Jan-11</td><td style="text-align: right;;">235</td><td style="text-align: right;;">30</td><td style="text-align: right;;">26</td><td style="text-align: right;;">18</td><td style="text-align: right;;">10</td><td style="text-align: right;;">77</td><td style="text-align: right;;">125</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">11200</td><td style="text-align: right;;">1-Jan-10</td><td style="text-align: right;;">26</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">11200</td><td style="text-align: right;;">1-Feb-10</td><td style="text-align: right;;">37</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">11200</td><td style="text-align: right;;">1-Mar-10</td><td style="text-align: right;;">41</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">21</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">11200</td><td style="text-align: right;;">1-Apr-10</td><td style="text-align: right;;">28</td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">11200</td><td style="text-align: right;;">1-May-10</td><td style="text-align: right;;">33</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">6</td><td style="text-align: right;;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">11200</td><td style="text-align: right;;">1-Jun-10</td><td style="text-align: right;;">34</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">33</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">11200</td><td style="text-align: right;;">1-Jul-10</td><td style="text-align: right;;">29</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">11200</td><td style="text-align: right;;">1-Aug-10</td><td style="text-align: right;;">51</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">3</td><td style="text-align: right;;">24</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">11200</td><td style="text-align: right;;">1-Sep-10</td><td style="text-align: right;;">45</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">7</td><td style="text-align: right;;">37</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;">11200</td><td style="text-align: right;;">1-Oct-10</td><td style="text-align: right;;">54</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">7</td><td style="text-align: right;;">49</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;">11200</td><td style="text-align: right;;">1-Nov-10</td><td style="text-align: right;;">44</td><td style="text-align: right;;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;">22</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;">11200</td><td style="text-align: right;;">1-Dec-10</td><td style="text-align: right;;">35</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">24</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;;">11200</td><td style="text-align: right;;">1-Jan-11</td><td style="text-align: right;;">37</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L4</th><td style="text-align:left">{=SUM(<font color="Blue">IF(<font color="Red">$A$3:$A$28=$L$2,IF(<font color="Green">YEAR(<font color="Purple">$B$3:$B$28</font>)=L$3,IF(<font color="Purple">$B$3:$B$28<=DATE(<font color="Teal">L$3,(<font color="#FF00FF">3*ROWS(<font color="Navy">L$4:L4</font>)</font>)+1,0</font>),$C$3:$I$28</font>)</font>)</font>)</font>)-SUM(<font color="Blue">L$3:L3</font>)+L$3}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Copy across & down...

RickM · Mar 8, 2011

Thanks to all for the help. Still having trouble getting the formula to work correctly. The items in green are correct the items in red are NOT.
G14 should =95
H14 should = 3
I14 should = 4
J14 should = 7
K14 should = 4
L14 should = 9
M14 should = 62
I am assuming if I fix row 14. I can figure out row 15 and 16.

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

12

Lookup Branch

APP

DEC

TOTAL

%Approved

A

B

C

D

E

F

N

13

11200

Q1

116

6

122

95%

104

5

4

3

49

14

Q2

103

34

137

75%

86

4

9

10

24

66

15

Q3

181

29

210

86%

143

11

19

8

6

23

140

16

Q4

131

15

146

90%

101

14

8

3

12

78

QtrData

Excel 2007

Cell Formulas
Range	Formula
C13	=SUM(G13:J13)
C14	=SUM(G14:J14)
C15	=SUM(G15:J15)
C16	=SUM(G16:J16)
D13	=SUM(K13:L13)
D14	=SUM(K14:L14)
D15	=SUM(K15:L15)
D16	=SUM(K16:L16)
E13	=C13+D13
E14	=C14+D14
E15	=C15+D15
E16	=C16+D16
F13	=C13/E13
F14	=C14/E14
F15	=C15/E15
F16	=C16/E16
G13	=SUM(OFFSET(DATA!$A$3,MATCH(QtrData!$A$13,DATA!$A$3:$A$752,0)-1,2,3,1))
G14	=SUM(OFFSET(DATA!$A$3,MATCH(QtrData!$A$13,DATA!$A$3:$A$752,0)*3,2,3,1))
G15	=SUM(OFFSET(DATA!$A$3,MATCH(QtrData!$A$13,DATA!$A$3:$A$752,0)*6,2,3,1))
G16	=SUM(OFFSET(DATA!$A$3,MATCH(QtrData!$A$13,DATA!$A$3:$A$752,0)*9,2,3,1))
H13	=SUM(OFFSET(DATA!$A$3,MATCH(QtrData!$A$13,DATA!$A$3:$A$752,0)-1,3,3,1))
H14	=SUM(OFFSET(DATA!$A$3,MATCH(QtrData!$A$13,DATA!$A$3:$A$752,0)*3,3,3,1))
H15	=SUM(OFFSET(DATA!$A$3,MATCH(QtrData!$A$13,DATA!$A$3:$A$752,0)*6,3,3,1))
H16	=SUM(OFFSET(DATA!$A$3,MATCH(QtrData!$A$13,DATA!$A$3:$A$752,0)*9,3,3,1))
I13	=SUM(OFFSET(DATA!$A$3,MATCH(QtrData!$A$13,DATA!$A$3:$A$752,0)-1,4,3,1))
I14	=SUM(OFFSET(DATA!$A$3,MATCH(QtrData!$A$13,DATA!$A$3:$A$752,0)*3,4,3,1))
I15	=SUM(OFFSET(DATA!$A$3,MATCH(QtrData!$A$13,DATA!$A$3:$A$752,0)*6,4,3,1))
I16	=SUM(OFFSET(DATA!$A$3,MATCH(QtrData!$A$13,DATA!$A$3:$A$752,0)*9,4,3,1))
J13	=SUM(OFFSET(DATA!$A$3,MATCH(QtrData!$A$13,DATA!$A$3:$A$752,0)-1,5,3,1))
J14	=SUM(OFFSET(DATA!$A$3,MATCH(QtrData!$A$13,DATA!$A$3:$A$752,0)*3,5,3,1))
J15	=SUM(OFFSET(DATA!$A$3,MATCH(QtrData!$A$13,DATA!$A$3:$A$752,0)*6,5,3,1))
J16	=SUM(OFFSET(DATA!$A$3,MATCH(QtrData!$A$13,DATA!$A$3:$A$752,0)*9,5,3,1))
K13	=SUM(OFFSET(DATA!$A$3,MATCH(QtrData!$A$13,DATA!$A$3:$A$752,0)-1,6,3,1))
K14	=SUM(OFFSET(DATA!$A$3,MATCH(QtrData!$A$13,DATA!$A$3:$A$752,0)*3,6,3,1))
K15	=SUM(OFFSET(DATA!$A$3,MATCH(QtrData!$A$13,DATA!$A$3:$A$752,0)*6,6,3,1))
K16	=SUM(OFFSET(DATA!$A$3,MATCH(QtrData!$A$13,DATA!$A$3:$A$752,0)*9,6,3,1))
L13	=SUM(OFFSET(DATA!$A$3,MATCH(QtrData!$A$13,DATA!$A$3:$A$752,0)-1,7,3,1))
L14	=SUM(OFFSET(DATA!$A$3,MATCH(QtrData!A13,DATA!$A$3:$A$752,0)*3,7,3,1))
L15	=SUM(OFFSET(DATA!$A$3,MATCH(QtrData!$A$13,DATA!$A$3:$A$752,0)*6,7,3,1))
L16	=SUM(OFFSET(DATA!$A$3,MATCH(QtrData!$A$13,DATA!$A$3:$A$752,0)*9,7,3,1))
M13	=SUM(OFFSET(DATA!$A$3,MATCH(QtrData!$A$13,DATA!$A$3:$A$752,0)-1,8,3,1))
M14	=SUM(OFFSET(DATA!$A$3,MATCH(QtrData!$A$13,DATA!$A$3:$A$752,0)*3,8,3,1))
M15	=SUM(OFFSET(DATA!$A$3,MATCH(QtrData!$A$13,DATA!$A$3:$A$752,0)*6,8,3,1))
M16	=SUM(OFFSET(DATA!$A$3,MATCH(QtrData!$A$13,DATA!$A$3:$A$752,0)*9,8,3,1))

DATA TABLE

Excel Workbook

A

B

C

D

E

F

G

H

I

1

2

Branch #

Date

A

B

C

D

E

F

N

3

11175

Jan/2010

120

10

19

18

10

38

70

4

11175

Feb/2010

117

11

23

17

10

75

70

5

11175

Mar/2010

234

22

14

20

10

90

141

6

11175

Apr/2010

261

34

30

27

10

86

137

7

11175

May/2010

253

36

19

23

10

59

116

8

11175

Jun/2010

307

35

51

25

12

83

142

9

11175

Jul/2010

298

24

42

25

10

42

110

10

11175

Aug/2010

219

19

15

19

18

54

111

11

11175

Sep/2010

85

16

15

8

7

27

63

12

11175

Oct/2010

65

6

1

2

3

15

42

13

11175

Nov/2010

86

1

14

9

2

27

58

14

11175

Dec/2010

223

25

32

12

85

152

15

11175

Jan/2011

235

30

26

18

10

77

125

16

11200

Jan/2010

26

2

1

14

17

11200

Feb/2010

37

1

2

1

2

14

18

11200

Mar/2010

41

2

1

21

19

11200

Apr/2010

28

2

1

2

15

20

11200

May/2010

33

2

3

1

6

14

21

11200

Jun/2010

34

1

2

1

33

22

11200

Jul/2010

29

3

2

1

3

16

23

11200

Aug/2010

51

4

1

3

24

11200

Sep/2010

45

2

1

7

37

25

11200

Oct/2010

54

3

2

3

7

49

26

11200

Nov/2010

44

3

2

3

22

27

11200

Dec/2010

35

2

24

28

11200

Jan/2011

37

2

4

3

15

DATA

Excel 2007

RickM · Mar 8, 2011

Here is a link to the file... http://www.box.net/shared/pi28ghovxt
Thanks for looking and helping out.

Haseeb Avarakkan · Mar 8, 2011

Enter a Quarter Year in A42, eg: 2010

G39, copy across & down...

=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)*(YEAR(DATA!$B$3:$B$752)=$A$42)*(DATA!$B$3:$B$752 < DATE($A$42,(3*ROWS(G$39:G41))+1,0))*INDEX(DATA!$C$3:$I$752,0,MATCH(G$38,DATA!$C$2:$I$2,0)))-SUM(G$38:G40)

RickM · Mar 8, 2011

Thanks for the reply but I get a circular reference when I paste that formula in g39. Can you look at this link to the workbook? Here is a link to the file... http://www.box.net/shared/pi28ghovxt the formulas are on the sheet QTRDATA

Haseeb Avarakkan · Mar 8, 2011

Sorry, my mistake.

Here is the correct one;

=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)*(YEAR(DATA!$B$3:$B$752)=$A$42)*(DATA!$B$3:$B$752 < DATE($A$42,(3*ROWS(G$39:G39))+1,0))*INDEX(DATA!$C$3:$I$752,0,MATCH(G$38,DATA!$C$2:$I$2,0)))-SUM(G$38:G38)

You need to enter a Quarter Year in A42.

Copy across & down...

RickM · Mar 8, 2011

I get 0 (zero) as the answer in all the cells.

Haseeb Avarakkan · Mar 8, 2011

Did you enter an year in A42? eg: 2010.

RickM · Mar 8, 2011

yes.

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

38

Lookup Branch

APP

DEC

TOTAL

%Approved

A

B

C

D

E

F

N

39

11200

Q1

0

#DIV/0!

0

40

Q2

0

#DIV/0!

0

41

Q3

0

#DIV/0!

0

42

2010

Q4

0

#DIV/0!

0

QtrData

Excel 2007

Cell Formulas
Range	Formula
C39	=SUM(G39:J39)
C40	=SUM(G40:J40)
C41	=SUM(G41:J41)
C42	=SUM(G42:J42)
D39	=SUM(K39:L39)
D40	=SUM(K40:L40)
D41	=SUM(K41:L41)
D42	=SUM(K42:L42)
E39	=C39+D39
E40	=C40+D40
E41	=C41+D41
E42	=C42+D42
F39	=C39/E39
F40	=C40/E40
F41	=C41/E41
F42	=C42/E42
G39	=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)(YEAR(DATA!$B$3:$B$752)=$A$42)(DATA!$B$3:$B$752< DATE($A$42,(3ROWS(G$39:G39))+1,0))INDEX(DATA!$C$3:$I$752,0,MATCH(G$38,DATA!$C$2:$I$2,0)))-SUM(G$38:G38)
G40	=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)(YEAR(DATA!$B$3:$B$752)=$A$42)(DATA!$B$3:$B$752< DATE($A$42,(3ROWS(G$39:G40))+1,0))INDEX(DATA!$C$3:$I$752,0,MATCH(G$38,DATA!$C$2:$I$2,0)))-SUM(G$38:G39)
G41	=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)(YEAR(DATA!$B$3:$B$752)=$A$42)(DATA!$B$3:$B$752< DATE($A$42,(3ROWS(G$39:G41))+1,0))INDEX(DATA!$C$3:$I$752,0,MATCH(G$38,DATA!$C$2:$I$2,0)))-SUM(G$38:G40)
G42	=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)(YEAR(DATA!$B$3:$B$752)=$A$42)(DATA!$B$3:$B$752< DATE($A$42,(3ROWS(G$39:G42))+1,0))INDEX(DATA!$C$3:$I$752,0,MATCH(G$38,DATA!$C$2:$I$2,0)))-SUM(G$38:G41)
H39	=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)(YEAR(DATA!$B$3:$B$752)=$A$42)(DATA!$B$3:$B$752< DATE($A$42,(3ROWS(H$39:H39))+1,0))INDEX(DATA!$C$3:$I$752,0,MATCH(H$38,DATA!$C$2:$I$2,0)))-SUM(H$38:H38)
H40	=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)(YEAR(DATA!$B$3:$B$752)=$A$42)(DATA!$B$3:$B$752< DATE($A$42,(3ROWS(H$39:H40))+1,0))INDEX(DATA!$C$3:$I$752,0,MATCH(H$38,DATA!$C$2:$I$2,0)))-SUM(H$38:H39)
H41	=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)(YEAR(DATA!$B$3:$B$752)=$A$42)(DATA!$B$3:$B$752< DATE($A$42,(3ROWS(H$39:H41))+1,0))INDEX(DATA!$C$3:$I$752,0,MATCH(H$38,DATA!$C$2:$I$2,0)))-SUM(H$38:H40)
H42	=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)(YEAR(DATA!$B$3:$B$752)=$A$42)(DATA!$B$3:$B$752< DATE($A$42,(3ROWS(H$39:H42))+1,0))INDEX(DATA!$C$3:$I$752,0,MATCH(H$38,DATA!$C$2:$I$2,0)))-SUM(H$38:H41)
I39	=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)(YEAR(DATA!$B$3:$B$752)=$A$42)(DATA!$B$3:$B$752< DATE($A$42,(3ROWS(I$39:I39))+1,0))INDEX(DATA!$C$3:$I$752,0,MATCH(I$38,DATA!$C$2:$I$2,0)))-SUM(I$38:I38)
I40	=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)(YEAR(DATA!$B$3:$B$752)=$A$42)(DATA!$B$3:$B$752< DATE($A$42,(3ROWS(I$39:I40))+1,0))INDEX(DATA!$C$3:$I$752,0,MATCH(I$38,DATA!$C$2:$I$2,0)))-SUM(I$38:I39)
I41	=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)(YEAR(DATA!$B$3:$B$752)=$A$42)(DATA!$B$3:$B$752< DATE($A$42,(3ROWS(I$39:I41))+1,0))INDEX(DATA!$C$3:$I$752,0,MATCH(I$38,DATA!$C$2:$I$2,0)))-SUM(I$38:I40)
I42	=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)(YEAR(DATA!$B$3:$B$752)=$A$42)(DATA!$B$3:$B$752< DATE($A$42,(3ROWS(I$39:I42))+1,0))INDEX(DATA!$C$3:$I$752,0,MATCH(I$38,DATA!$C$2:$I$2,0)))-SUM(I$38:I41)
J39	=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)(YEAR(DATA!$B$3:$B$752)=$A$42)(DATA!$B$3:$B$752< DATE($A$42,(3ROWS(J$39:J39))+1,0))INDEX(DATA!$C$3:$I$752,0,MATCH(J$38,DATA!$C$2:$I$2,0)))-SUM(J$38:J38)
J40	=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)(YEAR(DATA!$B$3:$B$752)=$A$42)(DATA!$B$3:$B$752< DATE($A$42,(3ROWS(J$39:J40))+1,0))INDEX(DATA!$C$3:$I$752,0,MATCH(J$38,DATA!$C$2:$I$2,0)))-SUM(J$38:J39)
J41	=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)(YEAR(DATA!$B$3:$B$752)=$A$42)(DATA!$B$3:$B$752< DATE($A$42,(3ROWS(J$39:J41))+1,0))INDEX(DATA!$C$3:$I$752,0,MATCH(J$38,DATA!$C$2:$I$2,0)))-SUM(J$38:J40)
J42	=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)(YEAR(DATA!$B$3:$B$752)=$A$42)(DATA!$B$3:$B$752< DATE($A$42,(3ROWS(J$39:J42))+1,0))INDEX(DATA!$C$3:$I$752,0,MATCH(J$38,DATA!$C$2:$I$2,0)))-SUM(J$38:J41)
K39	=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)(YEAR(DATA!$B$3:$B$752)=$A$42)(DATA!$B$3:$B$752< DATE($A$42,(3ROWS(K$39:K39))+1,0))INDEX(DATA!$C$3:$I$752,0,MATCH(K$38,DATA!$C$2:$I$2,0)))-SUM(K$38:K38)
K40	=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)(YEAR(DATA!$B$3:$B$752)=$A$42)(DATA!$B$3:$B$752< DATE($A$42,(3ROWS(K$39:K40))+1,0))INDEX(DATA!$C$3:$I$752,0,MATCH(K$38,DATA!$C$2:$I$2,0)))-SUM(K$38:K39)
K41	=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)(YEAR(DATA!$B$3:$B$752)=$A$42)(DATA!$B$3:$B$752< DATE($A$42,(3ROWS(K$39:K41))+1,0))INDEX(DATA!$C$3:$I$752,0,MATCH(K$38,DATA!$C$2:$I$2,0)))-SUM(K$38:K40)
K42	=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)(YEAR(DATA!$B$3:$B$752)=$A$42)(DATA!$B$3:$B$752< DATE($A$42,(3ROWS(K$39:K42))+1,0))INDEX(DATA!$C$3:$I$752,0,MATCH(K$38,DATA!$C$2:$I$2,0)))-SUM(K$38:K41)
L39	=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)(YEAR(DATA!$B$3:$B$752)=$A$42)(DATA!$B$3:$B$752< DATE($A$42,(3ROWS(L$39:L39))+1,0))INDEX(DATA!$C$3:$I$752,0,MATCH(L$38,DATA!$C$2:$I$2,0)))-SUM(L$38:L38)
L40	=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)(YEAR(DATA!$B$3:$B$752)=$A$42)(DATA!$B$3:$B$752< DATE($A$42,(3ROWS(L$39:L40))+1,0))INDEX(DATA!$C$3:$I$752,0,MATCH(L$38,DATA!$C$2:$I$2,0)))-SUM(L$38:L39)
L41	=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)(YEAR(DATA!$B$3:$B$752)=$A$42)(DATA!$B$3:$B$752< DATE($A$42,(3ROWS(L$39:L41))+1,0))INDEX(DATA!$C$3:$I$752,0,MATCH(L$38,DATA!$C$2:$I$2,0)))-SUM(L$38:L40)
L42	=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)(YEAR(DATA!$B$3:$B$752)=$A$42)(DATA!$B$3:$B$752< DATE($A$42,(3ROWS(L$39:L42))+1,0))INDEX(DATA!$C$3:$I$752,0,MATCH(L$38,DATA!$C$2:$I$2,0)))-SUM(L$38:L41)
M39	=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)(YEAR(DATA!$B$3:$B$752)=$A$42)(DATA!$B$3:$B$752< DATE($A$42,(3ROWS(M$39:M39))+1,0))INDEX(DATA!$C$3:$I$752,0,MATCH(M$38,DATA!$C$2:$I$2,0)))-SUM(M$38:M38)
M40	=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)(YEAR(DATA!$B$3:$B$752)=$A$42)(DATA!$B$3:$B$752< DATE($A$42,(3ROWS(M$39:M40))+1,0))INDEX(DATA!$C$3:$I$752,0,MATCH(M$38,DATA!$C$2:$I$2,0)))-SUM(M$38:M39)
M41	=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)(YEAR(DATA!$B$3:$B$752)=$A$42)(DATA!$B$3:$B$752< DATE($A$42,(3ROWS(M$39:M41))+1,0))INDEX(DATA!$C$3:$I$752,0,MATCH(M$38,DATA!$C$2:$I$2,0)))-SUM(M$38:M40)
M42	=SUMPRODUCT((DATA!$A$3:$A$752=$A$39)(YEAR(DATA!$B$3:$B$752)=$A$42)(DATA!$B$3:$B$752< DATE($A$42,(3ROWS(M$39:M42))+1,0))INDEX(DATA!$C$3:$I$752,0,MATCH(M$38,DATA!$C$2:$I$2,0)))-SUM(M$38:M41)

Vlookup/Sumif/or CSE formula quandry?

RickM

Board Regular

Gerald Higgins

Well-known Member

Excel Facts

Haseeb Avarakkan

Well-known Member

RickM

Board Regular

RickM

Board Regular

Haseeb Avarakkan

Well-known Member

RickM

Board Regular

Haseeb Avarakkan

Well-known Member

RickM

Board Regular

Haseeb Avarakkan

Well-known Member

RickM

Board Regular

Similar threads

Forum statistics

Share this page

Vlookup/Sumif/or CSE formula quandry?

Board Regular

Well-known Member

Excel Facts

Well-known Member

Board Regular

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock