Vlookup/Sumif/or CSE formula quandry?

RickM

Board Regular
Joined
Dec 5, 2007
Messages
84
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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

RickM

Board Regular
Joined
Dec 5, 2007
Messages
84
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.
 

stefankemp

Board Regular
Joined
Mar 11, 2010
Messages
136
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.
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115

ADVERTISEMENT

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.
 

RickM

Board Regular
Joined
Dec 5, 2007
Messages
84
Gerald, I am getting the dreaded #N/A with that formula? I don't understand the -1,2,3,1)) part.
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115

ADVERTISEMENT

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

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

RickM

Board Regular
Joined
Dec 5, 2007
Messages
84
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.
 

RickM

Board Regular
Joined
Dec 5, 2007
Messages
84
Yes....What do I change in the formula to get same results for column D? then I can duplicate that across the columns?
 

Forum statistics

Threads
1,141,727
Messages
5,708,127
Members
421,548
Latest member
douae

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?

Disable AdBlock

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
Top