Excel question

[pete]

Anyone able to answer an excel question (I suspect they are straightforward ones but i might be wrong).

I am trying to add up some values in our roster. The roster shows all our shifts and they are all of different lengths I want the excel sheet to add up all that persons' shifts for the month.
So it would assign 7 hours for a M, 8 for a "A", 9 for a "N" etc.

Help?

[BiggestNizzy]

Can you post some sample data?

[pete]

Pm'd you a link

[campbell]

Interested to have a try too, if Nizzy bombs out. Which he won't

[greido]

I would use a vlookup, this link explains it pretty well: http://www.contextures.com/xlFunctions02.html

[pete]

I unleashed Mrs Pete on it too...

Campbell you have email about a visit?

[BiggestNizzy]

Campbell thanks for the vote of confidence but I am a little rusty, that and I have a lackey (trainee) for this sort of thing now

I tried creating a 3rd page with this folmula in each cell

=VLOOKUP(Sheet1!E6,Sheet2!$A$1:$B$9,2,TRUE) but it brings in the wrong value

=vlookup(It looks to the initial page for the code letter,checks the array on sheet 2, replaces the code letter with the corresponding time column 2, find an exact value)

I will tie in with the lackey tomorrow he might even write a macro for it

[campbell]

Apols Pete, only just back from LCY and behind on email

DECODE function may help.

Or send it over, Pete

[woody]

Can't you use IF AND? Setting the and value to 0> to be counted? Would need to see it to think it out (& be at work with work laptop, sadly I'm not for a week).

[Dominic]

Can't you use IF AND? Setting the and value to 0> to be counted? Would need to see it to think it out (& be at work with work laptop, sadly I'm not for a week).

I would think IF statement within IF statement would do the trick.

[graeme]

PM'd you a link to a sheet to try.

I find (with any programming problem), rather than find the 1 uber function that does it all, break the problem down into steps. It's easier to read, maintain etc.

I added a cell to count the 'M's, another to count the 'A's etc. Then look up the M value (7) and multiply that by the M count. Then added a total column for all the shifts added together. You might not want it laid out by month like I did, but you'll get the idea of adding intermediate steps. Hope that helps.

[Sanjøy]

Afternoon looking for some help. I am having a moment. I have two columns, one with text colour of sauce and one with the volume of sauce. I want to output the number of each volume of sauce by colour. That makes terrible Engerlish I know.

Source matrix:

Volume Sauce
3 Brown
2 Red
4 Red
3 Red
1 Brown
5 Brown

Desired output:

Red Brown
1s 0 1
2s 1 0
3s 1 1
4s 1 0
5s 0 1


tia

S

[neil]

Countifs() will do it for you: