Formula help needed (please)

[bionicle]

Hi all,

Once again I turn to those with a superior mind to mine (not that hard to be honest ), I've tried index match, Vlookup & SumProduct with no luck.

Need some assistance with a formula, what I'm trying to achieve is:
total number of hours based on a dropdown box however the hours need only are based on a code for each row.

the drop down has 4No headings
the hours columns total 3No and the code is 1No column.
therefore if heading one in cell J1 reads need ten i get a return of the column D based on the letter reference in column C (this action is needed for 3No of the headings the other heading requires a total of all three columns.
hope this makes sense.

example:

If J1 reads "Even Nicer" the result in column H for row 1 code A would be 0 where if J1 read "Need" it would read 8
the codes in Column C would be in random order thus the need for column H to take into account the code reference.
The 3No heading plus an "All"

Dropdown J1

Code	Need	Nice	Even Nicer		Hours		Even Nicer
A	8	0	0
B	8	0	0
C	0	.5	1
E	0	.8	0
F	0	.3	0
G	0	.3	2
H	0	0	1
I	0	0	0
J	0	1	0
K	0	2	.8

 

[James006]

Hi,
Your explanation is not clear at all

Which Column Letter corresponds to your set-up ???

 

[bionicle]

Just read it back to myself and can only agree with you...
on the chart above; column "8" (column H) has a dropdown in the first row "H1" with the list built with four headings Need, Nice, Even Nicer, All.
in column 6 (Column F) lets say F1 I need a formula that references cell "H1" (the dropdown) and returns the hours based on the column headings in column "B, C & D or all three if "All" is selected. I also need it to reference the code used if I input the value "G" say in column "E1" Cell F1 Would read 2, if I changed the dropdown to all Cell F1 would be 2.3

Hope this makes more sense

 

[James006]

OK ...
Basically, there are two inputs :
1. Cell H1 for Need, Nice, Even Nicer, All
2. Cell E2 for A,B, C, etc ...
Formula to be tested for cell F2:

=IF(E2="",SUM(OFFSET($A$2:$A$11,0,MATCH($H$1,{"Need";"Nice";"Even Nicer"},0))),IF(H1="All",SUMPRODUCT(($B$2:$D$11)*($A$2:$A$11=E2)),SUMPRODUCT((OFFSET($A$2:$A$11,0,(MATCH($H$1,{"Need";"Nice";"Even Nicer"},0))))*($A$2:$A$11=E2))))

 

[bionicle]

Not to worry, solved the formula

 

[James006]

Good ... How ???

 

[Fluff]

Good to hear you got the solution.
If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[bionicle]

I looked at it in a different way, and used the following:

=SUMPRODUCT(O77:R77*--($O$6:$R$6=$N$2))
Instead of using code column I simplified the solution by just looking at the times produced by the formula I already had in place.