Excel 2007 : Extracting and adding numbers

**Brian.gronski** · 03-09-2011, 10:21 PM

I am trying to create a spreadsheet where I have 7 days in a week and a W# or a R# among several different ones, but I just want to add up the numbers in the 7 boxes, but only for certain codes.

Approved Codes: A-Absent, L-Late Ask, N-Accepted, Did Not Work As Agreed, O-Out of Department, R-Refused, S-Sick Leave, U-Union Rep, W-Worked

I want it to charge under L-Late Ask, W-Worked and O-Out of Department

so it would look like...

Sun l mon l tues l wed l thur l fri l Sat l final worked
w2 l r2 l r2 l w2 w1r1 l r2 l w2 l 7

so it would add the Worked codes but not add other codes specified.
I'm not the best at excel and I'd love for this to be a lot more managable and user friendly spread sheet.for my co-workers.

**MarvinP** · 03-10-2011, 12:55 AM

Hi Brian,

See the attached. It is an example of what I think you are working toward. See if it gives you some ideas.

**Brian.gronski** · 03-10-2011, 01:05 AM

actually I am going to attatch a copy to give a more clear idea. it needs to look simple, be able to be printable in either a full or blank format so you could fill it out in pen or just enter the data directly into the spread sheet.

**MarvinP** · 03-10-2011, 01:14 AM

Hey Brian,

I must be dense, cuz I just don't get it. r2 means what? r5 means what?

**Brian.gronski** · 03-10-2011, 01:28 AM

Originally Posted by MarvinP

Hey Brian,

I must be dense, cuz I just don't get it. r2 means what? r5 means what?

at the bottom of the sheet every letter repersents something. R - Refused overtime, W means Worked. ETC and the number is how many hours they refused/worked.

**DonkeyOte** · 03-10-2011, 04:16 AM

Based solely on the example posted:

Please Login or Register  to view this content.

Note array entry requirement

If you need to deal with decimal hours or hours >=10 let us know.

**MarvinP** · 03-10-2011, 10:13 AM

OK Brian,

Thanks for the "number of hours" as I was thinking number of times per day, so I was dense.
Find the attached Pivot Table again. The dates can be filtered to Last Week or Last Month or any timeframe you desire. You can also collapse the detail or reverse Employee and Code to your liking.

See if this pivot table does it for you.