Star Tech Excel Functions

SUM SUM - Adds all numbers in a specified ra

Bill No Product Qty Rate Total

ax-01 Jeans 6 650 3900
ax-02 T-shirt 3 720 2160
ax-03 Coat 5 950 4750
ax-04 Pants 2 680 1360
ax-05 Jacket 7 600 4200
ax-06 Trouser 5 250 1250
ax-07 Sweater 4 300 1200
ax-08 Dress 2 500 1000
ax-09 Pajamas 3 200 600
ax-10 Hat 9 150 1350
Grand Total 21770
numbers in a specified range of cells.
 SUMIF SUMIF: Adds values based on a single con

Bill No Product Qty Rate Total T-shirt Total Jeans Total

ax-01 Jeans 6 650 3900 7910 600
ax-02 T-shirt 3 720 2160
ax-03 T-shirt 5 950 4750
ax-04 Pants 2 680 1360
SUMIFS: Adds values based on multiple co
ax-05 Jeans 7 600 4200
ax-06 Jeans 5 250 1250
ax-07 Jeans 4 300 1200 Sumifs
ax-08 T-shirt 2 500 1000 3160
ax-09 Pajamas 3 200 600
ax-10 Hat 9 150 1350
lues based on a single condition

Hat Total
1350

lues based on multiple conditions

 PRODUCT PRODUCT: Multiplies ever

Bill No Product Qty Rate Total Product

ax-01 Jeans 6 650 3900 3900
ax-02 T-shirt 3 720 2160 2160
ax-03 Coat 5 950 4750 4750
ax-04 Pants 2 680 1360 1360
ax-05 Jeans 7 600 4200 4200
ax-06 Trouser 5 250 1250 1250
ax-07 Sweater 4 300 1200 1200
ax-08 Dress 2 500 1000 1000
ax-09 Pajamas 3 200 600 600
ax-10 Hat 9 150 1350 1350
PRODUCT: Multiplies everything together

Sum Product
21770
 MAX MAX: Returns the largest value in a given range of numbers

Students Name Total Marks Max

Suresh 482 482
Akash 276
Mohan 373
Deepak 253
Tarun 440 MAXIFS: Finds the largest value in a range based on one or more criteria
Devesh 475
Manish 418
Pankaj 446 Max + ifs
Tarun 375 440
Suresh 282
he largest value in a given range of numbers

gest value in a range based on one or more criteria

 MIN MIN: Finds the smallest number in a range

Students Name Total Marks Min

Suresh 482 253
Akash 276
Mohan 373
Deepak 253
Tarun 440 MINIFS: Finds the smallest number in a range based on specified condi
Devesh 475
Manish 418
Pankaj 446 Min + IFS
Tarun 375 282
Suresh 282
nge based on specified conditions
 LEN: Returns the number of characters in a text string, inclu
LEN
Id Numbers Number Digit Len
ax00 2485 10 10
ax65859745 10
ax2569 6
ax65987 7
ax475 8956 10 SUBSTITUTE: Replaces specific instances of old text with new text w
ax265895 8
ax64 4
ax59748662 10 Len + Substitute
ax456 5 8
ax78594 7
s the number of characters in a text string, including spaces

ces specific instances of old text with new text within a text string
 COUNT: Counts the number of cells that contain numbers
COUNT

Names Salary Count

Suresh 25769 6
Akash 24384
Mohan
Deepak 37175
Tarun
COUNTBLANK: Counts the number of empty cells wi
Devesh 34778
Manish
Pankaj 36392
Kalicharan Countblank
Poonam 12547 4
of cells that contain numbers within a specified range

e number of empty cells within a specified range

COUNTA
COUNTA: Counts the number of non-empty cells in a specified range, including
cells with numbers, text, dates, and other types of data
Names
Suresh
Akash Counta
Mohan 7

Tarun
Devesh

Pankaj

Poonam
cified range, including
es of data
 COUNTIF
Stu_Names Mon Tue Wed Thu Fri
Suresh P A P A P
Akash P P P A P
Mohan A P A P A
Suresh P P A P P
Tarun A P A A P
Devesh P P P P A
Akash A P A P P
Pankaj P P A P P
Suresh A P A P P
Poonam P A A A A

COUNTIF: Counts the number of cells in a range that meet a single specified condition

Stu_Names Mon Tue Wed Thu Fri

Suresh P A P A P
Suresh P P A P P
Devesh P P P P A
Pankaj P P A P P
Poonam P A A A A
Sat Individual Present individual Absent
P 4 2
P 5 1
P 3 3
A 4 2
P 3 3
P 5 1
A 3 3
P 5 1
P 4 2
P 2 4

Sat
P
A
P
P
P
COUNTIFS function in Excel is used to count the number of cells that meet multiple
conditions across one or more ranges.

Name City Age Countifs ----> Find the number of count where city
Alice Mumbai 25 2
Bob Delhi 30
Charlie Mumbai 25
David Mumbai 35
Eve Delhi 25
umber of count where city is mumbai and age is 25
 AVERAGE function: In Excel, it finds the average of a group of number
them up and dividing the sum by the total count of numbe
AVERAGE

Product Jeans T-shirt Coat

January 600 1200 1500
February 800 1000 1100
March 1800 500 800
April 600 2100 400
May 5000 4800 550
June 900 6000 350

Per Month Average 1616.666667 2600 783.3333333

 average of a group of numbers by adding
m by the total count of numbers
 PROPER
Name List First Character Capital
ram pal Ram Pal
jatin singh Jatin Singh
om prakash Om Prakash
kiran kumari Kiran Kumari
harish kumar Harish Kumar
mohan lal Mohan Lal
PROPER function : capitalizes the first letter of each word in a
text string, converting all other letters to lowercase
 UPPER
Name List All Character Capital
ram pal RAM PAL
jatin singh JATIN SINGH
om prakash OM PRAKASH
kiran kumari KIRAN KUMARI
harish kumar HARISH KUMAR
mohan lal MOHAN LAL
UPPER: The UPPER function converts all letters in a text
string to uppercase
 CONCAT
First Name Last Name All Character Capital
ram pal rampal
jatin singh jatin singh
om prakash om prakash
kiran kumari kiran kumari
harish kumar harish kumar
mohan lal mohan lal
Joins two or more text strings together.Use CONCAT instead of
CONCATENATE because it's faster and more flexible.CONCATENATE still
works for older files but is not recommended anymore

CONCAT
ram, pal
 LARGE
Large: Returns the kth (any) largest number from
Product Total Sales
Jeans 1800
T-shirt 12000
Coat 2500 LARGE
Pants 3000 88000
Jeans 15000
Trouser 52000
Sweater 120000
Dress 28000
Pajamas 88000
Hat 7800
ny) largest number from the range.
 SMALL
Product Total Sales Small: Returns the kth (any) smallest number from
the range
Jeans 52000
T-shirt 12000
Coat 88000 SMALL
Pants 3000 3000
Jeans 15000
Trouser 49000
Sweater 120000
Dress 28000
Pajamas 8000
Hat 7800
ny) smallest number from
ange
 The ROMAN function in Excel converts a number
ROMAN into its Roman numeral equivalent.

Numbers Roman
1 I
2 II
3 III
4 IV
5 V
6 VI
7 VII
8 VIII
9 IX
10 X
Excel converts a number
meral equivalent.
 IF IF - Returns a value based on

Names Product Total Sales Prize

Suresh Jeans 1800 Tab
Akash T-shirt 12000 Tab
Mohan Coat 2500 Tab
Deepak Pants 3000 Tab
Tarun Jeans 15000 Tab
Devesh Trouser 52000 Laptop
Manish Sweater 120000 Laptop
Pankaj Dress 28000 Tab
Kalicharan Pajamas 88000 Laptop
Poonam Hat 7800 Tab
eturns a value based on a specified condition
 LEFT - Returns a specified number of characters
LEFT from the start of a text string

Weeks Name Left 3 Char

Sunday Sun
Monday Mon
Tuesday Tue
Wednesday Wed
Thursday Thu
Friday Fri
Saturday Sat
d number of characters
of a text string
 RIGHT - Returns a specified number of characters
RIGHT from the end of a text string

Mobile Numbers Right 4 Digit

9869763158 3158
9821441211 1211
9824504472 4472
9855429411 9411
9897577790 7790
9829994110 4110
9893606468 6468
ed number of characters
of a text string
 COUNTBLANK: Counts the number of empty
COUNTBLANK cells within a specified range

Names Mobile No.

Suresh 9904188701 Count Blank Cells
Akash 9913599048 3
Mohan
Deepak 9890759911
Tarun 9975441399
Devesh 9941249804
Manish
Pankaj 9858415270
Kalicharan
Poonam 9989694230
he number of empty
cified range
 NOW - Returns the current date a
NOW

10/23/2025 9:34

10/23/2025 9:34
- Returns the current date and time
 TODAY - Returns the current
date
TODAY
Yesterday 10/22/2025
Today 10/23/2025
Tomorrow 10/24/2025
 The TIME function in Excel returns the time value based on th
given hour, minute, and second. The syntax is =TIME(hour,
Time minute, second)

3:31 PM
cel returns the time value based on the
second. The syntax is =TIME(hour,
 MONTH - Extracts the month
from a date.
MONTH
Days Months
100 4
 DATEDIF - Calculates the difference between
two dates in days, years, or months.
DATEDIF
Date of Birth 12/8/2002
Year 22
Months 10
Day 15
s the difference between
ys, years, or months.