Summing a range

[general]

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

 

[Juan Pablo González]

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]

See (not all records shown)...

SalesSummary.xls
	C	D	E	F	G	H	I	J
1		Date	Sales				2002
2		28-Sep-02	197				October
3		29-Sep-02	70		4	34	3375
4		30-Sep-02	44
5		1-Oct-02	130
6		2-Oct-02	92
7		3-Oct-02	81
8		4-Oct-02	126
9		5-Oct-02	5
10		6-Oct-02	79
11		7-Oct-02	62
12		8-Oct-02	35
13		9-Oct-02	147
14		10-Oct-02	104
15		11-Oct-02	9
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.

 

[Juan Pablo González]

Wow Aladin, this is one i didn't know...

MONTH(I2&0)

Nice !