ASSIGNMENT – 4

Salary Sheet

Use of Formulas – SUM, Nested IF, COUNTA, COUNTIF, SUMIF & VLOOKUP

Salary Data

Name Department Post Basic DA 2.5% HRA 3.5% PF 1.5% Total Grade
AMIT COMPUTER MANAGER 12,000          
NEHA COMPUTER SUPERVISOR 8,500          
ROHAN COMPUTER ACCOUNTANT 6,500          
PRIYA ELECTRICAL GUARD 5,500          
VIKAS ELECTRICAL CASHIER 9,000          
KIRAN ELECTRICAL ACCOUNTANT 10,500          
ARUN FINANCE MANAGER 15,000          
POOJA FINANCE GUARD 6,000          
RAVI FINANCE SUPERVISOR 11,000          
SIMRAN COMPUTER GUARD 5,000          

Questions

Q.1 COUNTIF Formula – Department-wise Employees

Using the COUNTIF formula, calculate how many employees are working in each of the following departments:

  1. COMPUTER

  2. ELECTRICAL

  3. FINANCE

Q.2 SUMIF Formula – Computer Department

Using the SUMIF formula, calculate the total Basic Salary of employees working in the COMPUTER department.

Q.3 VLOOKUP – ROHAN

Using the VLOOKUP formula, find the following details of ROHAN:

  1. Post

  2. Grade

Q.4 VLOOKUP – ARUN

Using the VLOOKUP formula, find the following details of ARUN:

  1. Post

  2. Grade

Q.5 COUNTIF Formula – Post-wise Employees

Using the COUNTIF formula, calculate how many employees are working as:

  1. MANAGER

  2. GUARD

Q.6 Calculate DA, HRA & PF

Calculate the following for each employee using appropriate formulas:

  • DA = Basic × 2.5%

  • HRA = Basic × 3.5%

  • PF = Basic × 1.5%

Q.7 SUM Formula – Total Salary

Calculate the Total Salary of each employee using the SUM formula.

Total Salary = Basic + DA + HRA − PF

Q.8 Nested IF – Grade

Using the Nested IF formula, assign grades according to the Total Salary:

Total Salary Grade
Greater than ₹20,000 A
Greater than ₹15,000 B
Otherwise C

Q.9 COUNTA Formula – Total Employees

Using the COUNTA formula, calculate the total number of employees in the salary sheet.

Q.10 SUMIF Formula – Post-wise Salary

Using the SUMIF formula, calculate:

  1. Total Basic Salary of all MANAGERS

  2. Total Basic Salary of all GUARDS

Formulas to Practice

SUM | Nested IF | COUNTA | COUNTIF | SUMIF | VLOOKUP

Important Calculation Rules

DA:
Basic × 2.5%

HRA:
Basic × 3.5%

PF:
Basic × 1.5%

Total Salary:
Basic + DA + HRA − PF

PreviousNext