Lookup problem

[squiggler47]

I was wondering if you have any ideas on this

See attached sheet!

I want to take only the D1 to D19 values and look them up in the table in row A then sum the values!, the problem is it has to ignore Numbers and Other values not starting with a D! These should total up in row 25 under each date!

The same with column K it should total the hours for D values Horizontaly!

To add a twist D1-D99 lookup column b in the table, D101-D199 lookup column C, D201-D299 lookup column D.

The Sheet is in Excel 2002 format!

Thanks

Darren!

[NBVC]

A little confusing...

You row/column references in your description above don't seem to match your spreadsheet (e.g. "To add a twist D1-D99 lookup column b in the table, D101-D199 lookup column C, D201-D299 lookup column D" ... column B is text, columns C-E have numbers.. but the titles don't match your description above).

Also what does this really refer to? : I want to take only the D1 to D19 values and look them up in the table in row A

is it Column A or Row 1 or what?

Please also reattach your sheet with some actual results you are expecting and explain where they came from.

[squiggler47]

Ok, i will try to explain

Cell M5:M23 contains the Duties worked on monday which can be D1,D2..D19 (thats a text Value) which refers to the table in columns B-E to get the hours for that particular duty!

These cells can also have a value (if someone does extra or none duty work!)

Or Hol,Sick (perhaps more)

So what i want to do for each column and row (m5:s23)is :-

find all cells in that column/row with text beginning with "D", look it up and total the value in column C:-

EG Row 5 contains D1,8,8,Sick,8,Hol,Hol

i want to lookup D1 in b5:b41, get the corresponding value in c5:c41 giving a result of 10

in column 2 we have D2,d3,d3,d3,d3,d101,d3

So i need values for D2=5,d3=8,d3=8,d3=8,d3=8,D101 - ignore for now,d3=8 giving a total of 44

I also need this vertically as well, since I need to verify that all duties have been accounted for!

the twist is that (these are values not cells) D101 = D1 in Saturday,D201=d1 in sunday/holidays

[NBVC]

Before we go to the 'twist', does this formula in H5, copied down take care part of the need?

=SUM($M5:$S5)+SUMPRODUCT(($M5:$S5=$B$5:$B$23)*$C$5:$C$23)

[squiggler47]

Yes, apart from it only needs to sum D values, not numbers!

[squiggler47]

It also needs to reference the whole table, incase duties are added, and do that vertically as well!

[NBVC]

So then just:

=SUMPRODUCT(($M5:$S5=$B$5:$B$23)*$C$5:$C$23)

Now, what's next?

[NBVC]

Just need to sum the columns and its good to go

Are you asking me this? If so, please elaborate.

If not, please mark thread as [Solved]

[squiggler47]

Yes, cant figure out how to get the formulas to work on the columns, i just get value errors :-(

[NBVC]

If you are talking about M25:S25, then try:

=SUMPRODUCT((M$5:M$23=$B$5:$B$23)*$C$5:$C$23)+SUMPRODUCT((TRANSPOSE(M$5:M$23)="D"&MID($B$5:$B$23,2,3)+100)*$D$5:$D$23)+SUMPRODUCT((TRANSPOSE(M$5:M$23)="D"&MID($B$5:$B$23,2,3)+200)*$E$5:$E$23)

in M25. This one has to be confirmed with CTRL+SHIFT+ENTER not just ENTER, because of the TRANSPOSE() function...

Then copy it across to S25.

[squiggler47]

Still doesnt work, same problem I was having I attached current state of sheet!

It doesnt seem to match the Duplicates

[NBVC]

I guess we have to transpose the first set also....I missed that one.

try:

=SUMPRODUCT((TRANSPOSE(N$5:N$23)=$B$5:$B$23)*$C$5:$C$23)+SUMPRODUCT((TRANSPOSE(N$5:N$23)="D"&MID($B$5:$B$23,2,3)+100)*$D$5:$D$23)+SUMPRODUCT((TRANSPOSE(N$5:N$23)="D"&MID($B$5:$B$23,2,3)+200)*$E$5:$E$23)

confirmed with CTRL+SHIFT+ENTER

[squiggler47]

Lol, yes missing transpose,

Thanks for all your help, I'm still getting my head around the SumProduct, strangely the books I have dont mention it!

I'm hoping my wife will be happy that I am making her job easier!

Regards

Darren

[squiggler47]

One more slight problem, I was trying to make the ranges dynamic to allow for new values, it doesnt seem to like name ranges in the formula!

Any ideas?

[squiggler47]

I need to allow for the list in rows B:E to expand and contract, I found one problem, if i can get the ranges to work dynamic then I should be good, going to try again!!!

[NBVC]

Dynamic in what way?

[squiggler47]

I think i got it for some reason my dynamic ranges didnt work first time, I redefined them now and all is good!

Again your help is much appreciated!