Summing a range

general

New Member
Joined
Oct 31, 2002
Messages
3
I feel sure that someone has solved this before me but I have honestly spent 2 hours surfing and have not found the answer. Please assist.
Workbook contains sales by day of the year. Rows are the day and type of sales are the columns. I want to be able to enter the month in a cell and have a formula sum the proper range of cells according to the month requested.
My thoughts were to use vlookup to get the range start and again for the range end but when inserting vlookup into the sum function I get #NAME? formula result. Here is the formula I am using . . have I typed something wrong?
101 = sheet name
column to sum E; rows 223 - 252
= correct result =+SUM('101'!E223:E252)

= error =+SUM('101'!E=VLOOKUP(D11,TABLES!G6:I17,2,FALSE):E=VLOOKUP(D11,TABLES!G6:I17,3,FALSE))

When I use the audit function and calculate each function, the answer to the vlookup is surrounded by brands ( ) and I think that is why I get the #name? message. How do I get the results to lose the brands and become the row value of the sum function?

I know the weekend has begun but I'll be back here trying to figure this out. TIA

da general journal
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
Hello... you need the INDIRECT function for this. For example, this

=INDIRECT("A" & 1)

will create a reference to the cell A1.

So, you need to do the same, but replace the 1 in my example with your VLOOKUP function. Hope that helps
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
See (not all records shown)...
SalesSummary.xls
CDEFGHIJ
1DateSales2002
228-Sep-02197October
329-Sep-02704343375
430-Sep-0244
51-Oct-02130
62-Oct-0292
73-Oct-0281
84-Oct-02126
95-Oct-025
106-Oct-0279
117-Oct-0262
128-Oct-0235
139-Oct-02147
1410-Oct-02104
1511-Oct-029
Data


The year of interest is in I1.
The month of interest is in I2.

Note that you can have dropdown lists in I1 and I2 in order to select a target year and a target month.

The formula in G3...

=MATCH(DATE(I1,MONTH(I2&0),1),INDEX(SALES,0,1))

computes the location of the start record.

The formula in H3...

=MATCH(DATE(I1,MONTH(I2&0)+1,0),INDEX(SALES,0,1))

computes the location of the end record.

SALES is the name assigned to data area from D2 on, using Insert|Name|Define.

The formula in I3...

=SUM(INDEX(SALES,G3,2):INDEX(SALES,H3,2))

totals the sales amounts of the target month and year.
 

Forum statistics

Threads
1,144,058
Messages
5,722,269
Members
422,419
Latest member
Havok390

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