I need help with the following problem: I was using the nested if function,
but due to limitation of seven levels only, i need to find alternative
solution to my problem.
I want to be able to use hlookup combined with the sum function to add up
noncontiguous numbers. Here's my formula to extract and sum data for June -
fringe:

z1=jun
=hlookup(z1,a1:g8,sum(2,6),false))
i want to add up fringe for month of june = 450
currently this formula yield = 0
2 3 4 5 6 7 8 9
1 jan feb mar apr may jun jul aug...dec
2Salary FT 500 550 600 500 550 600 500 550
3Hourly FT 400 450 500 400 450 500 400 450
4Fringe FT 300 350 400 300 350 400 300 350
5
6Salary PT 250 250 250 250 250 250 250 250
7Hourly PT 200 200 200 200 200 200 200 200
8Fringe PT 150 150 152 141 90 50 85 125

One idea to get there ..

Illustrated in this sample:
http://www.freefilehosting.net/download/3i9fj
sum discontiguous via lookup month n partial text.xls

In B1 across are the months in text; jan, feb,...
In A2:A8 are the text: Salary FT, Hourly FT, etc

Assume
Inputs for the month (eg: jun) are selected in Y1
Inputs for partial text in col A (eg: Fringe) are selected in Y2

Then in Y3:
=IF(COUNTA(Y1:Y2)<2,"",SUMPRODUCT(--(ISNUMBER(SEARCH(Y2,$A$2:$A$8))),OFFSET($A$2:$A$8, ,MATCH(Y1,$B$1:$J$1,0))))
will return the required results
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Amy" wrote:
I need help with the following problem: I was using the nested if function,
but due to limitation of seven levels only, i need to find alternative
solution to my problem.
I want to be able to use hlookup combined with the sum function to add up
noncontiguous numbers. Here's my formula to extract and sum data for June -
fringe:

z1=jun
=hlookup(z1,a1:g8,sum(2,6),false))
i want to add up fringe for month of june = 450
currently this formula yield = 0
2 3 4 5 6 7 8 9
1 jan feb mar apr may jun jul aug...dec
2Salary FT 500 550 600 500 550 600 500 550
3Hourly FT 400 450 500 400 450 500 400 450
4Fringe FT 300 350 400 300 350 400 300 350
5
6Salary PT 250 250 250 250 250 250 250 250
7Hourly PT 200 200 200 200 200 200 200 200
8Fringe PT 150 150 152 141 90 50 85 125