Help with obtaining multiple results using an array formula

[Mickey Juice]

Hey Everyone,
I'm just chasing some help as i'm an excel n008.


I'm running exl2002 on win XP and i'm trying to get multiple results from a data sheet using an array formula.

Basically I want to enter a code and get the coresponding figures so I can add them all up.
eg I want to get the total amount of damage for items with the code 1a (forklift damage).

I've attemped to use a Hlookup array, but it only returns the first value (correctly) then the 2nd value will be 0 and then nothing

Here's the table with the data (on sheet called "Data")

Reel_Damage 2005.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
14			Week Ending	Week Ending
15	1		Months of November & December		Damage caused by:	(code)	1a	1a	1a	1b	2a	1a	3a	4a	1a
16					Amount of Damage	(Kg's)	33.0	27.6	15.0	16.0	11.0	40.0	21.0	14.0	8.0
17	2		January 1	01/01/2005	Damage caused by:	(code)
18					Amount of Damage	(Kg's)
19	3		January 8	08/01/2005	Damage caused by:	(code)
20					Amount of Damage	(Kg's)
21	4		January 15	15/01/2005	Damage caused by:	(code)
22					Amount of Damage	(Kg's)
Data

Here's my attempt at the solution, just having one column for now (sheet called arrays)

Reel_Damage 2005.xls
	A	B	C	D
1	1a
2	33.00
3	0.00
4	#N/A
5	#N/A
6	#N/A
7	#N/A
8	#N/A
9	#N/A
10	#N/A
11	#N/A
12	#N/A
13	#N/A
14	#N/A
Arrays

Also:
Is it possible to get multiple results and have them added together all the same formula? rather than adding them all together once I get the result.

Another problem I have is I want to be able to enter a figure and get the results for that corresponding week eg week ending January 8. This is because the figures will be charted on a weekly basis...

Any ideas or solutions will be greatly appreciated!
ray:

 

[acw]

Hi

For the first part, how about using the Sumif formula. In A2 of the sheet arrays enter the formula
=SUMIF(Data!G15:O16,Arrays!A1,Data!G16:O16)
with adjustments to the range to suit.

Tony

 

[acw]

Hi

For the second part, put the date 1/1/05 in cell A4 of sheet Arrays. In B4 enter the formula
=SUMIF(OFFSET(Data!$G$1,MATCH($A4,Data!$D:$D,0)-1,0):OFFSET(Data!$O$1,MATCH($A4,Data!$D:$D,0)-1,0),$A$1,OFFSET(Data!$G$1,MATCH($A4,Data!$D:$D,0),0):OFFSET(Data!$O$1,MATCH($A4,Data!$D:$D,0),0))

Fill the dates down in column A and copy the formula in B4 down as required. It also needs A1 to contain the code.

HTH

Tony