Excel Range/Array Formula 1

[KatherineCoombs]

I have an Excel file with 2 sheets...one sheet is called labour1 and it contains what you would consider to be vlookup data in that it has the following columns:

Code Amount
52120 $100
52121 $250
52126 $65
etc etc


OK, on the other sheet, called Graph Info, we are trying to summarise this information based on what the codes mean to us, so it is where we would put the vlookup formula:

LABOUR BREAKDOWN:
Code Amount
52120 $100
52121:52129 $????

For the majority of the cases we can use a simple vlookup to return the amount, but I don't know how to get it to give me the sum of all the amounts on the labour1 sheet where the corresponding "code" falls into the range given in the Graph Info sheet.

You know what I mean? I'm guessing that for this one instance it's some combination array vlookup or something, but I don't know how to make it work - can you help please???

Thanks!!
Katherine

 

[ETID]

Look to the database functions,...

DSUM()...
Adds the numbers in a column in a list or database that match conditions you specify.

For more information and examples, click .

Syntax

DSUM(database,field,criteria)

Database is the range of cells that make up the list or database. A database is a list of related data in which rows of related information are records and columns of data are fields. The first row of the list contains labels for each column.

Field indicates which column is used in the function. Field can be given as text with the column label enclosed between double quotation marks, such as "Age" or "Yield," or as a number that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.

Criteria is the range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label for specifying a condition for the column.

also...

Database and List Management functions
DAVERAGE Returns the average of selected database entries

DCOUNT Counts the cells that contain numbers in a database

DCOUNTA Counts nonblank cells in a database

DGET Extracts from a database a single record that matches the specified criteria

DMAX Returns the maximum value from selected database entries

DMIN Returns the minimum value from selected database entries

DPRODUCT Multiplies the values in a particular field of records that match the criteria in a database

DSTDEV Estimates the standard deviation based on a sample of selected database entries

DSTDEVP Calculates the standard deviation based on the entire population of selected database entries

DSUM Adds the numbers in the field column of records in the database that match the criteria

DVAR Estimates variance based on a sample from selected database entries

DVARP Calculates variance based on the entire population of selected database entries

GETPIVOTDATA Returns data stored in a PivotTable

 

[xlbo]

If your numbers are all consistent lengths then something like:
=SUM((labour1!A1:A100>=value(left(A4,5)))*(labour1!A1:A100<=value(right(A4,5)))*(labour1!A1:A100))
array entered
should work Rgds
~Geoff~

 

[xlbo]

although I have to say that a database function is probably a better idea here - especially if you have a lot of these codes to check against.Depends on the layout of your summary worksheet I guess Rgds
~Geoff~

 

[KatherineCoombs]

Well I couldn't quite get the database function working well, but the SUM array formula works a treat. I've got some text codes (eg GA0000) as well as so I removed the &quot;value&quot; field and changed it from left (a4,5) to left (a4,6) and it works a treat. Thanks so much Geoff!

 

[ETID]

What no star?...I'll get it