macpam
asked on
Numbers 09 IF-THEN problem
I am new to involved formulas, this is what I am trying to accomplish. Each employee can work at multiple locations, so I have three locations per day possible. Each location can have a different supervisor or share both supervisors. I need to total the hours worked for "N" supervisor, regardless of location, for the entire week. So the supervisor is N or T or NA (for shared).
The top table is the timesheet portion. The bottom portion is to split out expenses for each supervisor. The IF-THEN goes in the bottom left table for totals of just that hours worked for that supervisors.
My beginner's attempt, bottom left table for Derek:
IF (HOURS : : C3=N, C4=N, C5=N, G3=N, G4=N, G5=N, K3=N, K4=N, K5=N, N3=N, N4=N, N5=N, O3=N, O4=N, O5=N, S3=N, S4=N, S5=N, W3=N, W4=N, W5=N)
THEN SHOW SUM OF ONLY THOSE SUB-TOTAL CELLS
(HOURS : : F3, F4, F5, J3, J4, J5, N3, N4, N5, R3, R4, R5, V3, V4, V5, Z3, Z4, Z5
I don't know these things: 1--How is "or" represented in the top part, the IF, (I don't want it to total all these) 2--How to get only the total for that supervisor and not have all these cell's totals used (use the SUP column in determining what to add).
Misc. point: The 5:00am-5:00am is the only way to not get errors in some of the other calculations. If left blank, it creates an error. So I did a zero hours. Looks odd, but it works.
Link to dropbox file:
numbers file
The top table is the timesheet portion. The bottom portion is to split out expenses for each supervisor. The IF-THEN goes in the bottom left table for totals of just that hours worked for that supervisors.
My beginner's attempt, bottom left table for Derek:
IF (HOURS : : C3=N, C4=N, C5=N, G3=N, G4=N, G5=N, K3=N, K4=N, K5=N, N3=N, N4=N, N5=N, O3=N, O4=N, O5=N, S3=N, S4=N, S5=N, W3=N, W4=N, W5=N)
THEN SHOW SUM OF ONLY THOSE SUB-TOTAL CELLS
(HOURS : : F3, F4, F5, J3, J4, J5, N3, N4, N5, R3, R4, R5, V3, V4, V5, Z3, Z4, Z5
I don't know these things: 1--How is "or" represented in the top part, the IF, (I don't want it to total all these) 2--How to get only the total for that supervisor and not have all these cell's totals used (use the SUP column in determining what to add).
Misc. point: The 5:00am-5:00am is the only way to not get errors in some of the other calculations. If left blank, it creates an error. So I did a zero hours. Looks odd, but it works.
Link to dropbox file:
numbers file
do you have any code so far?
Hello,
you have posted this in the Excel Section - yet it looks like you are using Apple Numbers.
In Excel you can use the function called sumifs() - however I don't think that will help you here.
If you can use excel can i ask you to export your file to a different fomrat as I cannot import your file into Excel.
Jens
you have posted this in the Excel Section - yet it looks like you are using Apple Numbers.
In Excel you can use the function called sumifs() - however I don't think that will help you here.
If you can use excel can i ask you to export your file to a different fomrat as I cannot import your file into Excel.
Jens
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I didn't think about Excel not being able to open the file. The client will be using Numbers on iPad. Since the formulas are pretty much the same, I listed Excel. Sorry.
I will look at https://discussions.apple.com/thread/3933680?start=0&tstart=0 this evening when I get home and back on this.
I will look at https://discussions.apple.com/thread/3933680?start=0&tstart=0 this evening when I get home and back on this.
ASKER
Finally got it done. Attacked it in stages, but it worked. Thanks for everyone's help.