Usage of IF Conditions
Hai,
I need help in how formulate a cell with conditions ,
If have 5 different persons with different salaries for 5 days
person 1- 2000
person 2- 3000
Person 3-4000
person 4-10000
person 5-2500
At each person set of dates are created with Present or Absent indicating as P and A
Based on the their Present status it has to calculate the per day salary of each person
At end of 5 th day , based on number of P's and A's it has to calculate the number of days Present and Absent .
Pls find the same illustration in EXcel sheet attached
Kindly help me how can i use IF statement in this case
with regards,
Siddhartha.M
| Attachment | Size |
|---|---|
| HR-Sample.xlsx | 8.1 KB |
IF, CountIF
Hello Sid,
I don't know how to attach a document on this website (so stupid I can't anywhere to attach something).
For starters, you are missing another Per Day Cost column at the end for July 5 2014.
Anyway, for each Column "Per Day Cost" use the following in D3:
=IF(D3="P",$C3,0)
Just copy and past this into the rest of the cells under Per Day Cost and it should adjust.
To explain in more detail: If the value in D3 is equal to "P", return the value in C3. As you copy and past this, the D3 will change but it will also pull from Column C based on the row you copy and pasted into.
For the Total, Present or Absent use:
=COUNTIF(D3:L3,"P")
=COUNTIF(E3:M3,"A")
This simply states, if there is a value of "P" or "A", count that cell. Therefore, Srinivas has 4 days present.
If you want the total amount they earned instead of the total number of days present/absent, you can simply just sum each of the columns like so:
=N3*C3
Again, Srinivas should make 20,000 because he was present for 4 days (N3) and makes 5000 a day (C3).
I hope this makes sense. Send me a PM if this site allows it and I can email you back your excel sheet if you want to see the finished product.