# Vlookup/Sumif/or CSE formula quandry?

#### RickM

##### Board Regular
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
ABCDEFGHI
2Branch #DateABCDEFN
311175Jan/2010120101918103870
411175Feb/2010117112317107570
511175Mar/20102342214201090141
611175Apr/20102613430271086137
711175May/20102533619231059116
811175Jun/20103073551251283142
911175Jul/20102982442251042110
1011175Aug/20102191915191854111
1111175Sep/2010851615872763
1211175Oct/20106561231542
1311175Nov/201086114922758
1411175Dec/20102232525321285152
1511175Jan/20112353026181077125
1611200Jan/201026211114
1711200Feb/2010371212214
1811200Mar/201041211121
1911200Apr/201028221215
2011200May/2010332231614
2111200Jun/201034122133
2211200Jul/201029321316
2311200Aug/20105141324
2411200Sep/2010452221737
2511200Oct/2010543323749
2611200Nov/201044332322
2711200Dec/201035224
2811200Jan/20113724315
DATA
Excel 2007

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Maybe I'm missing something, but why not just use
Code:
``=sum(C3:C5)``
?

I want to have a drop down that will list the branch # as the value to lookup. The return the first 3 rows and then the next 3 rows. I am summarizing rolling quarter numbers.

Quarter 2010 2011 2012
1 *
2
3
4

Add a table like above with the top left in cell K2 and enter the following formula where the * is (cell L3):

=SUMPRODUCT((\$A\$3:\$A\$28=11175)*(YEAR(\$B\$3:\$B\$28)=G\$3)*(INT((MONTH(\$B\$3:\$B\$28)-1)/3)+1=\$F2)*\$C\$3:\$C\$28)

Do a separate table for each of columns C to I.

Ah, that changes things.

Maybe this for the first quarter.
Assumes the first instance of 11175 is in cell A3.
Assumes your drop down to select the branch # is in cell K4.

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

This works on the data sample you posted.

You should be able to work out the formula for the second quarter from this.
If not, check if this works for quarter 1, and then post back.

Gerald, I am getting the dreaded #N/A with that formula? I don't understand the -1,2,3,1)) part.

Have you put something into K4 ?
Like 11175, or 11200 ?

If we can get this to work, I'll explain how it works.

I got it to work. It is a format issue. Coulmn A branch number is actually a linked table from Access. If I copy the value of A3 (11175) in K4 it works. When I try and copy Column A and Paste Special the formats into Column K it still does not work. Column A is formatted as "general". Thanks I am getting close.

Great, do you still need help ?

Yes....What do I change in the formula to get same results for column D? then I can duplicate that across the columns?

Replies
1
Views
275
Replies
6
Views
411
Replies
1
Views
442
Replies
20
Views
2K
Replies
4
Views
612

1,220,965
Messages
6,157,122
Members
451,399
Latest member
alchavar

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back