### 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.