Monday, October 2, 2023
Contents

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