+100 Excel Formula Examples

S No. What to do?
1 Count cells between dates
COUNTIFS
Example: =COUNTIFS($A$1:$A$20,”>=1/12/18″,$A$1:$A$20,”<=31/12/18″)
2 Count cells between two numbers
COUNTIFS
Example: =COUNTIFS($A$1:$A$20,”>=345″,$A$1:$A$20,”<=678″)
3 Count blank cells in range
COUNTBLANK
Example: =COUNTABLANK(A1:A10)
4 Count texts or any value in a range
COUNTA
Example: =COUNTA(A1:A10)
5 Count texts which contains any part text at start
COUNTIF
Example: =COUNTIF(A1:A10,”Excel*”)
6 Count texts which contains any part text at end
COUNTIF
Example: =COUNTIF(A1:A10,”*Excel”)
7 Count texts which contains any part text at any position
COUNTIF
Example: =COUNTIF(A1:A10,”*Excel*”)
8 Count cells greater than
COUNTIF
Example: =COUNTIF(A1:A10,”>10″)
9 Count cells less than
COUNTIF
Example: =COUNTIF(A1:A10,”<10″)
10 Count cells equal to
COUNTIF
Example: =COUNTIF(A1:A10,10)
11 Count cells that are blank
COUNTBLANK
Example: =COUNTBLANK(A1:A10)
12 Count cells which are negative in a range
COUNTIF
Example: =COUNTIF(A1:A10,”<0″)
13 Count cells which are positive in a range
COUNTIF
Example: =COUNTIF(A1:A10,”>0″)
14 Count items in list
COUNTIFS
Example: =COUNTIFS(A1:A10,”A”,B1:B10,”B”)
15 Count particular item in list
COUNTIF
Example: =COUNTIF(A1:A10,”A”)
16 Count dates which are older than
COUNTIF
Example: =COUNTIF(A1:A10,”<11/12/18″)
17 Count dates which are newer than
COUNTIF
Example: =COUNTIF(A1:A10,”>11/12/18″)
18 Count the visible rows after filtering data
SUBTOTAL
19 Sum up many numerical values in range
SUM
Example: =SUM(A1:A10)
20 Cumulative sum of a range
SUM
Example: =SUM($A$1:A1) and drag it up to down like =SUM($A$1:A10)
21 Sum by group of items
SUMIF
Example: =SUMIF(A1:A10,”A”,B1:B10)
22 Sum cells greater than
SUMIF
Example: =SUMIF(A1:A10,”>10″)
23 Sum cells less than
SUMIF
Example: =SUMIF(A1:A10,”<10″)
24 Sum cells equal to
SUMIF
Example: =SUMIF(A1:A10,10)
25 Sum if multiple criteria matched
SUMIFS
Example: =SUMIFS(A1:A10,”<15″,B1:B10,”Boy”)
26 Sum the no of vegetables in list only
SUMIF
Example: =SUMIF(A1:A10,”VEG”,B1:B10)
27 Sum if cell contains texts in another cell at start of text
SUMIF
Example: =SUMIF(A1:A10,”Excel*”)
28 Sum if cell contains texts in another cell at end of text
SUMIF
Example: =SUMIF(A1:A10,”*Excel”)
29 Sum if cell contains texts in another cell at any position of text
SUMIF
Example: =SUMIF(A1:A10,”*Excel*”)
30 Sum up the products of values in two or more thean two array
SUMPRODUCT
Example: SUMPRODUCT(A1:A10,B1:B10,C1;C10)
31 Get the total by multiplying items no with rate.
SUMPRODUCT
Example: SUMPRODUCT(A1:A10,B1:B10,C1;C10)
32 Sum if cells contain both a and b
SUMIFS
Example: =SUMIFS(A1:A10,”*a*”,A1:A10,”*b*”)
33 Sum up the visible rows after filtering data
SUBTOTAL
34 Sum by group of items
SUMIF
Example: =SUMIF(A1:A10,”A”)
35 Sum cells greater than
SUMIF
Example: =SUMIF(A1:A10,”>10″)
36 Sum cells less than
SUMIF
Example: =SUMIF(A1:A10,”<10″)
37 Sum cells equal to
SUMIF
Example: =SUMIF(A1:A10,10)
38 Sum the largest three values
SUMIFS, LARGE
Example: =SUMIFS(A1:A10,LARGE(A1:A2,1),A1:A10,LARGE(A1:A2,2),A1:A10,LARGE(A1:A2,3))
39 Sum the smallest three values
SUMIFS, SMALL
Example: =SUMIFS(A1:A10,SMALL(A1:A2,1),A1:A10,SMALL(A1:A2,2),A1:A10,SMALL(A1:A2,3))
40 Average numerical values
AVERAGE
Example: =AVERAGE(A1:A10)
41 Average numerical values with a criteria
AVERAGEIF
Example: =AVERAGEIF(A1:A10,10)
42 Average the largest three values
AVERAGEIFS, LARGE
Example: =AVERAGEIFS(A1:A10,LARGE(A1:A2,1),A1:A10,LARGE(A1:A2,2),A1:A10,LARGE(A1:A2,3))
43 Average the smallest three values
AVERAGEIFS, SMALL
Example: =AVERAGEIFS(A1:A10,SMALL(A1:A2,1),A1:A10,SMALL(A1:A2,2),A1:A10,SMALL(A1:A2,3))
44 Maximum value within a range
MAX
Example: =MAX(A1:A10)
45 Minimum value within a range
MIN
Example: =MIN(A1:A10)
46 Maximum value within a range with criteria
MAXIF, MAXIFS
47 Minimum value within a range with criteria
MINIF, MINIFS
48 kth largest value
LARGE
Example: for 3rd largest value in a range A1:A10 use this formula
=LARGE(A1:A10,3)
49 kth smallest value
SMALL
Example: for 3rd smallst value in a range A1:A10 use this formula
=SMALL(A1:A10,3)
50 Looking up values in columns of table vertically
VLOOKUP
Example: =VLOOKUP(A1, B1:D10,2,FALSE)
51 Looking up values in rows of table horizontally
HLOOKUP
Example: =HLOOKUP(A1, B1:D10,2,FALSE)
52 Looking value horizontally or vertically
INDEX, MATCH
Example: =INDEX(B1:E8,MATCH(B10,B1:B8,0),MATCH(B11,B1:E1,0))
53 Lookup the value from a table if matches certain criteria
IF, VLOOKUP
Example: =IF(A1>10,VLOOKUP(A1, B1:D10,2,FALSE),0)
54 Lookup the value from a table if doesn't matches certain criteria
IF, VLOOKUP, NOT
Example: =IF(NOT(A1>10),VLOOKUP(A1, B1:D10,2,FALSE),0)
55 Lookup the value from a table if matches multiple criteria at same time
IF, VLOOKUP, AND
Example: =IF(AND(A1>10,A1<55),VLOOKUP(A1, B1:D10,2,FALSE),0)
56 Lookup the value from a table if matches atleast one of the multiple criteria
IF, VLOOKUP, OR
Example: =IF(OR(A1>10,A1<55),VLOOKUP(A1, B1:D10,2,FALSE),0)
57 Lookup the value from a table if matches atleast one of the multiple criteria but not all at same time
IF, VLOOKUP, XOR
Example: =IF(XOR(A1>10,A1<55),VLOOKUP(A1, B1:D10,2,FALSE),0)
58 Lookup the value from a table if it contains part of any text as value
VLOOKUP
Example: =VLOOKUP(“*excel*”, B1:D10,2,FALSE)
59 Lookup the kth smallest value from a table array
VLOOKUP, SMALL
Example: =VLOOKUP(SMALL(C1:C10,3), B1:D10,2,FALSE)
60 Lookup the kth largest value from a table array
VLOOKUP, LARGE
Example: =VLOOKUP(LARGE(C1:C10,3), B1:D10,2,FALSE)
61 Lookup the smallest value from a table array
VLOOKUP, MIN
Example: =VLOOKUP(MIN(C1:C10), B1:D10,2,FALSE)
62 Lookup the largest value from a table array
VLOOKUP, MAX
Example: =VLOOKUP(MAX(C1:C10), B1:D10,2,FALSE)
63 Making a date from three different dates by taking year, month, day from each of
DATE, YEAR, MONTH, DAY
Example: =DATE(YEAR(A1),MONTH(B1),DAY(C1))
64 Return a text length
LEN
Example: =LEN(“Microsoft Excel”)
Returnes =15
65 Extract characters from start of text
LEFT
Example: =LEFT(“Microsoft Excel”, 3)
Returnes “Mic”
66 Extract characters from end of text
RIGHT
Example: =RIGHT(“Microsoft Excel”, 3)
Returnes “cel”
67 Extract characters from middle of text
MID
Example: =MID(“Microsoft Excel”, 3,2)
Returnes “cr”
68 If 3rd character of two text are same then return a value
IF, MID, EXACT
Example: =IF(EXACT(MID(A1,3,1),MID(B1,3,1)),”Similar 3rd”,”not similar”)
69 Extract PAN no from GST no (India)
MID
Example: If you are having GST no in A1 cell then use this formula =MID(A1, 3,10)
70 When having only first name and last name in a full name then extract first name
LEFT, FIND
Example: =LEFT(A1,FIND(” “,A1)-1)
71 When having only first name and last name in a full name then extract last name
RIGHT, LEN, FIND
Example: =RIGHT(A1,LEN(A1)-FIND(” “,A1))
72 Generic formula to separate words from text
TRIM, MID, SUBSTITUTE, REPT, LEN
Example: =TRIM(MID(SUBSTITUTE($A2,” “,REPT(” “,LEN($A2))),(C$1-1)*LEN($A2)+1,LEN($A2)))
Where A2 contains the text and C1 is the nth word in the text
73 Last date of current month
EOMONTH, TODAY
Example: =EOMONTH(TODAY(),0)
74 Return a value when a date is Sunday
IF, WEEKDAY
Example: =IF(WEEKDAY(A1)=1, “Sunday no work”, “Working Day”)
75 Calculate your age in years, months, days
DATEDIF, TODAY
Example if you have date of birth in A1
=DATEDIF(A1, TODAY(), “y”)& ” Years “& DATEDIF(A1, TODAY(), “ym”)&” Months “& DATEDIF(A1, TODAY(), “md”)& ” Days”
76 Calculate your age in fraction of years
YEARFRAC, TODAY
Example if you have date of birth in A1
=YEARFRAC(A1,TODAY(),1)
77 Get difference of a date from today
TODAY, DAYS, ABS
Example: =ABS(DAYS(TODAY(),”23/09/2018″))
78 Get workday after numbers of workdays from today
WORKDAY, TODAY
Example: =WORKDAY(TODAY(),56)
79 Get workday after numbers of workdays from today, modifying weekend holiday
WORKDAY.INTL, TODAY
Example: =WORKDAY.INTL(TODAY(),56,”0000010″)
It makes offday on Friday
80 Get net workdays between today and another date
NETWORKDAY, TODAY
Example: =NETWORKDAYS(TODAY(),”11/11/2018″)
81 Get net workdays between today and another date, modifying weekend holiday
NETWORKDAY.INTL, TODAY
Example: =NETWORKDAYS.INTL(TODAY(),”11/11/2018″,”0000010″)
It makes offday on Friday
82 Show a date as day of week
TEXT
Example: Suppose you are having date value in A1 cell
=TEXT(A1,”dddd”)
83 Show date as long format date
TEXT
Example: Suppose you are having date value in A1 cell
=TEXT(A1,”dd-mmmm-yyyy, dddd”)
84 Count column numbers of an array
COLUMNS
Example: =COLUMNS(A1:D10)
85 Count row numbers of an array
ROWS
Example: =ROWS(A1:D10)
86 Total numbers of cells in an array
COLUMNS, ROWS
Example: =COLUMNS(A1:D10)*ROWS(A1:D10)
87 Check whether multiple texts are same
EXACT, AND
Example: =AND(EXACT(A1,B1),EXACT(A1,C1),EXACT(A1,D1))
88 Join name and surname in one text and make it in proper case
CONCATENATE, PROPER
Example: =PROPER(CONCATENATE(A1&” “&B1))
89 Create random date between two dates
RANDBETWEEN
Example: =RANDBETWEEN(“11/6/2018”,TODAY())
90 Create random number between two numbers
RANDBETWEEN
Example: =RANDBETWEEN(23,32)
91 Random number from fixed set of options
CHOOSE, RANDBETWEEN, ROUND
Example: =CHOOSE(ROUND(RANDBETWEEN(1,5),0),34,78,12,93,19)
92 Random text values from fixed set of options
CHOOSE, RANDBETWEEN, ROUND
Example:
=CHOOSE(ROUND(RANDBETWEEN(1,5),0),”a”,”b”,”c”,”d”,”e”)
93 Remove numbers of characters text from end
LEFT, LEN
Example: If you need to remove 5 characters from a text from cell A1 =LEFT(A1, (LEN(A1)-5))
94 Remove numbers of characters text from start
RIGHT, LEN
Example: If you need to remove 5 characters from a text from cell A1 =RIGHT(A1, (LEN(A1)-5))
95 Finding exact middle character(s) of text
MID, LEN, MOD, IF
Example: If you have ext in A1 cell
=IF(MOD(LEN(A1),2)=1, MID(A1, ((LEN(A1)-1)/2+1), 1), MID(A1, LEN(A1)/2, 2))
This formula returns one character if the text length is odd and two if it is even.
96 Rounddown a number in the multiple of 5
FLOOR
Example: =FLOOR(567,5)
97 Roundup a number in the multiple of 5
CEILING
Example: =CEILING(567,5)
98 Square root of number
SQRT
Example: =SQRT(64)
99 Square root of number
POWER
Example: =POWER(64,0.5)
100 Cube root of number
POWER
Example: =POWER(27,(1/3))
101 Square of a number
POWER
Example: =POWER(20,2)
102 Cube of number
POWER
Example: =POWER(2,3)
103 Return a value when square root is a whole number
SQRT, MOD, IF
Example: =IF(MOD(SQRT(A1),1)=0,”Square”,”NA”)
104 Finding the closest value in an array from higher side
MIN, IF
Example: =MIN(IF(A1:A10>24,A1:A10))
Then Press Ctrl Shift Enter
105 Finding the closest value in an array from lower side
MAX, IF
Example: =MAX(IF(A1:A10<24,A1:A10))
Then Press Ctrl Shift Enter