Trending Now
SUM of Digits when cell Contains all Numbers
If you cell contains only numbers like A1:= 7654045, then following formula can be used to find sum of digits
=SUMPRODUCT(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))
SUM of Digits when cell Contains Numbers and non Numbers both
If you cell contains non numbers apart from numbers like A1:= 76$5a4b045%d, then following formula can be used to find sum of digits
=SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,ROW(1:9),"")))*ROW(1:9))
The above formula can be used even if contains all numbers as well.
A List is Unique or Not (Whether it has duplicates)
Assuming, your list is in A1 to A1000. Use following formula to know if list is unique.
=MAX(FREQUENCY(A1:A1000,A1:A1000)) =MAX(INDEX(COUNTIF(A1:A1000,A1:A1000),,))
If answer is 1, then it is Unique. If answer is more than 1, it is not unique.
Count No. of Unique Values
Use following formula to count no. of unique values –
=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))
Count No. of Unique Values Conditionally
If you have data like below and you want to find the unique count for Region = “A”, then you can use below Array formula –
=SUM(IF(FREQUENCY(IF(A2:A20<>"",IF(A2:A20="A",MATCH(B2:B20,B2:B20,0))),ROW(A 2:A20)-ROW(A2)+1),1))
If you have more number of conditions, the same can be built after A2:A20 = “A”.
Note – Array Formula is not entered by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don’t put { } manually.
Add Month to or Subtract Month from a Given Date
Very often, you will have business problems where you have to add or subtract month from a given date. One scenario is calculation for EMI Date.
Say, you have a date of 10/22/14 (MM/DD/YY) in A1 and you want to add number of months which is contained in Cell B1.
The formula in this case would be
=EDATE(A1,B1) [Secondary formula =DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1)) ]
Now, you want to subtract month which is contained in Cell B1.
=EDATE(A1,-B1) [Secondary formula =DATE(YEAR(A1),MONTH(A1)-B1,DAY(A1)) ]
Add Year to or Subtract Year from a Given Date
In many business problems, you might encounter situations where you will need to add or subtract years from a given date.
Let’s say A1 contains Date and B1 contains numbers of years. If you want to add Years to a given date, formulas would be –
=EDATE(A1,12*B1) =DATE(YEAR(A1)+B1,MONTH(A1),DAY(A1))
If you want to subtract Years from a given date, formulas would be –
=EDATE(A1,-12*B1) =DATE(YEAR(A1)-B1,MONTH(A1),DAY(A1))
Convert a Number to a Month Name
Use below formula to generate named 3 lettered month like Jan, Feb..Dec
=TEXT(A1*30,"mmm")
Replace “mmm” with “mmmm” to generate full name of the month like January, February..December in any of the formulas in this post.
Converting Date to a Calendar Quarter
Assuming date is in Cell A1. You want to convert it into a quarter (1, 2, 3 & 4). Jan to Mar is 1, Apr to Jun is 2, Jul to Sep is 3 and Oct to Dec is 4.
=CEILING(MONTH(A1)/3,1) OR =ROUNDUP(MONTH(A1)/3,0) OR =CHOOSE(MONTH(A1),1,1,1,2,2,2,3,3,3,4,4,4)
Converting Date to a Indian Financial Year Quarter
Assuming date is in Cell A1. You want to convert it into a Indian Financial Year Quarter. Jan to Mar is 4, Apr to Jun is 1, Jul to Sep is 2 and Oct to Dec is 3.
=CEILING(MONTH(A1)/3,1)+IF(MONTH(A1)<=3,3,-1) OR =ROUNDUP(MONTH(A1)/3,0)+IF(MONTH(A1)<=3,3,-1) OR =CHOOSE(MONTH(A1),4,4,4,1,1,1,2,2,2,3,3,3)
Calculate Age from Given Birthday
=DATEDIF(A1,TODAY(),"y")&" Years "&DATEDIF(A1,TODAY(),"ym")&" Months "&DATEDIF(A1,TODAY(),"md")&" Days"
Number to Date Format Conversion
If you have numbers like 010216 and you want to convert this to date format, then the following formula can be used
=--TEXT(A1,"00\/00\/00") for 2 digits year
Note – Minimum 5 digits are needed for above formula to work
If you have numbers like 01022016 and you want to convert this to date format, then the following formula can be used
=--TEXT(A1,"00\/00\/0000") for 4 digits year
Note – Minimum 7 digits are needed for above formula to work
Number to Time Format Conversion
If you have numbers like 1215 and you want to convert this to hh:mm format, then the following formula can be used
=--TEXT(A1,"00\:00")
Note – Minimum 3 digits are needed for above formula to work To convert to hh:mm:ss format
=--TEXT(A1,"00\:00\:00")
Note – Minimum 5 digits are needed for above formula to work
Count Cells Starting (or Ending) with a particular String
1.Say you want to count all cells starting with C
=COUNTIF(A1:A10,"c*")
c* is case insensitive. Hence, it will count cells starting with both c or C. Suppose you want to find all cells starting with Excel.
=COUNTIF(A1:A10,"excel*")
2.For ending
=COUNTIF(A1:A10,"*c")
c* is case insensitive. Hence, it will count cells starting with both c or C. Suppose you want to find all cells starting with Excel.
=COUNTIF(A1:A10,"*excel")
Count No. of Cells Having Numbers Only
COUNT function counts only those cells which are having numbers. Assuming your range is A1:A10, use following formula
=COUNT(A1:A10)
Count No. of Cells which are containing only Characters
Hence, if your cell is having a number 2.23, it will not be counted as it is a number. Use below formula considering your range is A1:A10
=COUNTIF(A1:A10,"*")
Number of Characters in a String without considering blanks
Say, you have a string like Vijay A. Verma and I need to know how many characters it has. In this case, it has 12 including decimal and leaving blanks aside.
Use below formula for the same –
=LEN(SUBSTITUTE(A1," ",""))
Number of times a character appears in a string
Suppose you want to count the number of times, character “a” appears in a string
=LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),"a",""))
Count Non Numbers in a String
Suppose you have a string “abc123def45cd” and you want to count non numbers in this. If your string is in A1, use following formula in A1
=IF(LEN(TRIM(A1))=0,0,SUMPRODUCT(--NOT(ISNUMBER((-- MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))))
Count Numbers in a String
Suppose you have a string “abc123def43cd” and you want to count numbers in this. If your string is in A1, use following formula –
=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,ROW(1:10)-1,"")))
OR
=SUMPRODUCT(--ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))
Count only Alphabets in a String
Suppose you have a string “Ab?gh123def%h*” and you want to count only Aphabets. Suppose your string is in A1, put following formula for this.
=SUMPRODUCT(LEN(A1)- LEN(SUBSTITUTE(UPPER(A1),CHAR(ROW(INDIRECT("65:90"))),"")))
OR
=SUMPRODUCT(--(ABS(77.5- CODE(MID(UPPER(A1),ROW(INDIRECT("A1:A"&LEN(A1))),1)))<13))
Most Frequently Occurring Value in a Range
Assuming, your range is A1:A10, enter the below formula as Array Formula i.e. not by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don’t put { } manually.
=INDEX(A1:A10,MATCH(MAX(COUNTIF(A1:A10,A1:A10)),COUNTIF(A1:A10,A1:A10),0))
The non-Array version of above formula
=INDEX(A1:A10,MATCH(MAX(INDEX(COUNTIF(A1:A10,A1:A10),,)),INDEX(COUNTIF(A1: A10,A1:A10),,),0))
COUNTIF on Filtered List
You can use SUBTOTAL to perform COUNT on a filtered list but COUNTIF can not be done on a filtered list. Below formula can be used to perform COUNTIF on a filtered list
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2,ROW(B2:B20)-ROW(B2),))*(B2:B20>14))
Here B2:B20>14 is like a criterion in COUNTIF (=COUNTIF(B2:B20,”>14″))
SUMIF on Filtered List
You can use SUBTOTAL to perform SUM on a filtered list but SUMIF can not be done on a filtered list. Below formula can be used to perform SUMIF on a filtered list
=SUMPRODUCT(SUBTOTAL(9,OFFSET(B2,ROW(B2:B20)-ROW(B2),))*(B2:B20>14))
Here B2:B20>14 is like a criterion in SUMIF.
Extract First Name from Full Name
=LEFT(A1,FIND(" ",A1&" ")-1)
Extract Last Name from Full Name
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))
Extract the Initial of Middle Name
Suppose, you have a name John Doe Smith and you want to show D as middle initial. Assuming, your data is in A1, you may use following formula
=IF(COUNTIF(A1,"* * *"),MID(A1,FIND(" ",A1)+1,1),"")
If name is of 2 or 1 words, the result will be blank. This works on 3 words name only as middle can be decided only for 3 words name.
Extract Middle Name from Full Name
=IF(COUNTIF(A1,"* * *"),MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-(FIND(" ",A1)+1)),"")
=IF(COUNTIF(A1,"* * *"),TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1)),2),FIND(" ",A1)+1,LEN(A1))),"")
=IF(COUNTIF(A1,"* * *"),LEFT(REPLACE(A1,1,FIND(" ",A1),""),FIND("
",REPLACE(A1,1,FIND(" ",A1),""))-1))
Remove Middle Name in Full Name
=IF(COUNTIF(A1,"* * *"),LEFT(A1,FIND(" ",A1&" "))&TRIM(RIGHT(SUBSTITUTE(A1,"
",REPT(" ",LEN(A1))),LEN(A1))),"")
=IF(COUNTIF(A1,"* * *"),REPLACE(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1),""),"")
Extract Integer and Decimal Portion of a Number
To extract Integer portion, one of the below can be used –
=INT(A1) =TRUNC(A1)
Positive value in A1 – If A1 contains 84.65, then answer would be 84. Negative value in A1 – If A1 contains -24.39, then answer would be -24.
If you want only +ve value whether value in A1 is -ve or +ve, the formula can have many variants.
=INT(A1)*SIGN(A1) OR =TRUNC(A1)*SIGN(A1) =INT(ABS(A1)) OR =TRUNC(ABS(A1)) =ABS(INT(A1)) OR = ABS(TRUNC(A1))
To extract Decimal portion –
=MOD(ABS(A1),1) =ABS(A1)-INT(ABS(A1))
Positive value in A1 – If A1 contains 84.65, then answer would be 0.65. Negative value in A1 – If A1 contains -24.39, then answer would be 0.39.
