advance excel

SUMIFS in Excel to find a conditional sum of values based on multiple criteria.

Syntax:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2]...)

Arguments:

sum_range → Cells to add
criteria_range1 → Range to check condition
criteria1 → Condition
You can add multiple criteria pairs

Date	Salesperson	Product	Region	Sales Amount
01-01-2025	Ravi	Laptop	East	50000
02-01-2025	Amit	Mobile	West	20000
03-01-2025	Ravi	Laptop	East	45000
05-01-2025	Suman	Tablet	North	15000
07-01-2025	Ravi	Mobile	West	25000
10-01-2025	Amit	Laptop	East	55000
12-01-2025	Suman	Laptop	North	30000
15-01-2025	Ravi	Tablet	East	20000

1. Find total sales of Ravi

2. Find total sales of Laptop in East region.

3. Find total sales by Amit in East region.

4. Find total sales greater than 30000.

5. Find total sales between 01-01-2025 and 10-01-2025.

6. Find total sales of Laptop by Ravi in East region.

7. Total sales of West region

8. Total sales of Laptop after 05-01-2025

9. Total sales of Ravi excluding Tablet

10. Total sales between 20000 and 50000

11. Total sales of North region before 12-01-2025

12. Total sales of Mobile in West region above 20000

13. Total sales where salesperson name starts with "R"

14. Total sales where product contains "top"

COUNTIFS function counts cells across multiple ranges based on one or several conditions

Syntax:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]...)

Arguments:

criteria_range1 → Range to check condition
criteria1 → Condition
You can add multiple criteria pairs

Roll No	Name	Class	Subject	Marks	Result
101	Ravi	10th	Math	85	Pass
102	Amit	10th	English	35	Fail
103	Suman	9th	Math	75	Pass
104	Ravi	10th	Science	40	Pass
105	Neha	9th	English	55	Pass
106	Amit	10th	Math	95	Pass
107	Suman	9th	Science	30	Fail
108	Ravi	9th	Math	60	Pass

1. Count how many students are in 10th class

2. Count how many students passed

3. Count how many students failed

4. Count Ravi in 10th class

5. Count students who scored more than 70

6. Count 9th class students who passed

7. Count students who failed in Science

8. Count students whose name starts with "R"

9. Count students scoring between 50 and 90

10. Count 10th class students who scored above 80

11. Count 9th class students who scored below 50

12. Count students who passed in Math

13. Count students who failed and scored below 40

14. Count students in 10th class excluding Ravi

15. Count students whose name contains letter "a"

16. Count students who scored between 60 and 90 in Math

17. Count students who passed but scored less than 50

AVERAGEIFS in Excel to find a conditional average of values based on multiple criteria.

Syntax:

=AVERAGEIFS(AVG_range, criteria_range1, criteria1, [criteria_range2, criteria2]...)

Arguments:

AVG_range → Cells to AVERAGE
criteria_range1 → Range to check condition
criteria1 → Condition
You can add multiple criteria pairs

Date	Salesperson	Product	Region	Sales Amount
01-01-2025	Ravi	Laptop	East	50000
02-01-2025	Amit	Mobile	West	20000
03-01-2025	Ravi	Laptop	East	45000
05-01-2025	Suman	Tablet	North	15000
07-01-2025	Ravi	Mobile	West	25000
10-01-2025	Amit	Laptop	East	55000
12-01-2025	Suman	Laptop	North	30000
15-01-2025	Ravi	Tablet	East	20000

1. Find average sales of Ravi

2. Find average sales of Laptop in East region.

3. Find average sales by Amit in East region.

4. Find average sales greater than 30000.

5. Find average sales between 01-01-2025 and 10-01-2025.

6. Find average sales of Laptop by Ravi in East region.

7. average sales of West region

8. average sales of Laptop after 05-01-2025

9. average sales of Ravi excluding Tablet

10. average sales between 20000 and 50000

11. average sales of North region before 12-01-2025

12. average sales of Mobile in West region above 20000

13. average sales where salesperson name starts with "R"

14. average sales where product contains "top"

Financial Functions

These functions are used for Loan, EMI, Investment & Interest calculations.

· rate → Interest rate per period

· nper → Total number of periods

· pmt → Payment made each period

· pv → Present value (optional)

· type → 0 = End of month (default)
1 = Beginning of month

1️⃣ FV (Future Value)

🔹 Definition:Calculates the future value of an investment.

Syntax:

=FV(rate, nper, pmt, [pv], [type])

Example: Fv=fv(rate/12,time*12,pmt)

2️⃣ PMT (EMI Calculation)

Definition:Calculates loan EMI amount.

Syntax:

=PMT(rate, nper, pv, [fv], [type])

Eg:pmt(rate/12,time*12,pv)

3️⃣ IPMT (Interest Payment)

Definition:Calculates interest part of EMI for a specific month.

Syntax:

=IPMT(rate, per, nper, pv)

Eg:ipmt(rate/12,1,time*12,pv)

4️⃣ PPMT (Principal Payment)

Definition:Calculates principal part of EMI for a specific month.

Syntax:

=PPMT(rate, per, nper, pv)

Example:ppmt=pmt-ipmt

5️⃣ RATE

Definition:Calculates interest rate per period.

Syntax:

=RATE(nper, pmt, pv, [fv], [type])

Example:Rate=rate(time*12,pmt,loan amount)

6️⃣ NPER

Definition:Calculates number of payment periods.

Syntax:

=NPER(rate, pmt, pv, [fv], [type])

Example:Nper=nper(rate/12,pmt,pv)

7️⃣ PV (Present Value)

Definition:Calculates current value of future payments.

Syntax:

=PV(rate, nper, pmt, [fv], [type]

Example: pv=pv(rate/12,time*12,pmt)

Loan Installment Calculation

Date & Time Formulas

Date & Time formulas are used for attendance, salary sheet, age calculation, deadlines, dashboards.

NOW()

Definition:Returns current date and time.

=NOW()

DATE()

Definition:Creates a date from year, month, day.

=DATE(2025, 2, 25)

👉 Result: 25-02-2025

TIME()

Definition:Creates time from hour, minute, second.

=TIME(10, 30, 0)

👉 Result: 10:30 AM

DAY(), MONTH(), YEAR()

Extract parts from a date.

=DAY(A2)

=MONTH(A2)

=YEAR(A2)

DATEDIF()

Used for Age Calculation

=DATEDIF(A2, TODAY(), "Y")

👉 Returns age in years.

Other options:

"M" → Months
"D" → Days

EOMONTH()

Returns last date of month

=EOMONTH(A2, 0)

👉 0 = current month
👉 1 = next month

WORKDAY()

🔹 Adds working days (excluding weekends)

=WORKDAY(A2, 10)

👉 Adds 10 working days.

NETWORKDAYS()

Counts working days between two dates

=NETWORKDAYS(A2, B2)

TEXT() (Date Formatting)

=TEXT(A2, "dd-mm-yyyy")

HOUR(), MINUTE(), SECOND()

Extract time parts:

=HOUR(A2)

=MINUTE(A2)

=SECOND(A2)

Task	Start Date	End Date	Total Days	Working Days	Month End
Task A	01-02-2025	10-02-2025
Task B	05-02-2025	20-02-2025
Task C	10-02-2025	28-02-2025

Name	Date of Birth	Today	Age (Years)	Age (Months)	Age (Days)
Ravi	15-08-1995	=TODAY()
Amit	10-01-2000	=TODAY()
Neha	25-12-1998	=TODAY()

Name	Date	In Time	Out Time	Total Hours	Late (After 9:00 AM)
Ravi	25-02-2025	09:15 AM	05:30 PM
Amit	25-02-2025	08:50 AM	05:00 PM
Neha	25-02-2025	09:05 AM	04:45 PM