Scottish Elises

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?

Can you post some sample data?

Pm'd you a link

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

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

I unleashed Mrs Pete on it too...

Campbell you have email about a visit?

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

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

DECODE function may help.

Or send it over, Pete

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).

woody wrote: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.

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.

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

Countifs() will do it for you: