1.1 Calculate SUM of digits of a numeric value in a cell

This formula can be used to calculate the sum of digits, when an excel cell contains only numbers like 7654045.

    =SUMPRODUCT(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

1.2 Calculate SUM of digits of an alphanumeric value in a cell

This formula can be used to calculate the sum of digits in a string where the string contains numeric and non numeric characters. e.g. consider the following string "67-5a4b045#d". the below formula will provide an output of 31 by calculating the sum of all 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.

1.3 Count No. of unique values

Use following formula to count no. of unique values

    =SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))

1.4 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 create { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Do not type { } manually.

1.5 Count No. of numeric cells

COUNT function counts only those cells which are having numbers. Assuming your range is A1:A10, use following formula

    =COUNT(A1:A10)

1.6 Extract Integer / Decimal part from 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.

1.7 roman representation of numbers

      Use ROMAN() function.
      = ROMAN(56) will give LVI.

ROMAN works only for numbers 1 to 3999.

1.8 Sum bottom N values in a range

Suppose you have numbers in range A1:A100 and you want to sum up bottom N values

    =SUMPRODUCT(SMALL($A$1:$A$100,ROW(1:10))) 
  
    In case, you want to ignore 0 values (and blanks)
    =SUMPRODUCT(SMALL(IF($A$1:$A$100<>0,$A$1:$A$100),ROW(1:10)))

Both the above formulas will function only if there are at least N values as per ROW(1:N). Hence, for above formulas, it would work only if there are at least 10 numbers in A1 to A100.

Enter the below formulas as Array Formula

    =SUM(IFERROR(SMALL($A$1:$A$100,ROW(1:10)),0))
    =SUM(IFERROR(SMALL(IF($A$1:$A$100<>0,$A$1:$A$100),ROW(1:10)),0))

Non Array Versions of above formulas (For Excel 2010 and above)

    =SUMPRODUCT(AGGREGATE(15,6,$A$1:$A$100,ROW(1:10)))
    =SUMPRODUCT(AGGREGATE(15,6,$A$1:$A$100/($A$1:$A$100<>0),ROW(1:10)))

1.9 Sum every Nth row

If your numbers are in range A1:A100, use below formula

      =SUMPRODUCT((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,2)=0))

Above formula is for every 2nd row. Replace 2 with N. Hence, for every 5th row

      =SUMPRODUCT((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,5)=0))

This is a generic formula and will work for any range. If you range is B7:B50, your formula would become

      =SUMPRODUCT((B7:B50)*(MOD(ROW(B7:B50)-ROW(B7)+1,2)=0))

1.10 MEDIANIF and MODEIF?

Excel doesn't provide MEDIANIF and MODEIF. You will have to use Array formulas to achieve these functionality. Let's assume that our data is like below

To calculate MEDIANIF and MODEIF, enter below formulas 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.

      =MEDIAN(IF(A2:A13="M",B2:B13))
      =MODE(IF(A2:A13="M",B2:B13))

      =AGGREGATE(16,6,(B1:B13)/(A1:A13="m"),50%)

      =INDEX(B1:B20,MATCH(MAX(INDEX((COUNTIF(B1:B20,B1:B20)*(A1:A20="m")),,)),IND
      EX((COUNTIF(B1:B20,B1:B20)*(A1:A20="m")),,),0))

1.11 Calculate Geometric Mean (ignore 0 and -ve values)

Geometric Mean is a useful mean and is applied only for +ve values. Hence, you will need to ignore <=0 values while calculating Geometric Mean. It is generally used where %ages are involved. For example, population growth for first year is 30%, for second year is 25% and for third year, it is 15%. Then Geometric Mean is used to calculate not Arithmetic Mean.

    Generally, Geometric Mean is calculated by the formula =GEOMEAN(A1:A10)

It would give error if the range contains <=0 values. There are various ways to deal with it and most commonly used way is to ignore <=0 values while calculating Geometric Mean. To ignore <=0 values, you must use an Array formula i.e. which must be entered by pressing CTRL+SHIFT+ENTER.

    =GEOMEAN(IF(A1:A10>0,A1:A10))

The above formula takes into account only those values which are positive.

Bonus Tip - When %age growth are involved, you will need to use following ARRAY formula to calculate Geometric Mean

    =GEOMEAN(IF(A1:A10>0,(1+A1:A10)))-1

Don't forget to format your result as %age.

1.12 Find Nth Largest number when there are duplicates

You know the LARGE function which can find the nth largest value. Hence, if you have a series like below

And you give =LARGE(A1:A10,3), you get the answer as 18

Now, if we have a series like below

Now, you give =LARGE(A1:A10,3) and now the result is 24. The reason is that large function gives the nth largest value in a sorted array. Hence, LARGE function will sort the above array as {24,24,24,22,22,18,18,9} and 3rd largest is 24.

But actually you want the unique 3rd largest which is 18 as the answer. The formula for such case would be

  =LARGE(IF(FREQUENCY($A$2:$A$10,$A$2:$A$10)<>0,$A$2:$A$10),3)

1.13 COUNTIF for non-contiguous range

All of us love COUNTIF. And it is very easy to do - just say =COUNTIF("A1:A100",">5") and it finds all the values within the range A1 to A100 which are greater than 5. But what if I wanted the result for only A3, A8 and it should omit other cells. Try putting in following formula

  =COUNTIF((A3, A8),">5") and it will give you #VALUE error.
  

  A possible solution is
  =(A3>5)+(A8>5)

What happens if you need to do for A3, A4, A5, A8, A24, A40, A45, A89. Now, you will have to use a formula like

  =(A3>5)+(A4>5)+(A5>5)+(A8>5)+(A24>5)+(A40>5)+(A45>5)+(A89>5)

The formula becomes cumbersome as the number of cells increase. In this case, you can use below formula. This single formula can take care of contiguous (like A3:A5) and noncontiguous ranges both

  =SUM(COUNTIF(INDIRECT({"A3:A5","A8","A24","A40","A45","A89"}),">5"))

1.14 Numerology Sum of the Digits (aka Sum the Digits till the result is a single digit)

In Numerology, it is often a task to add the digits till the result is a single digit. For example,

  74 = 7 + 4 = 11 = 1 + 1 = 2
  23 = 2 + 3 = 5
  78 = 7 + 8 = 15 = 1 + 5 = 6
  1234567 = 1 + 2 + 3 + 4 + 5 + 6 + 7 = 28 = 2+ 8 = 10 = 1+ 0 = 1

  =MOD(A1-1,9)+1

1.15 Generate sequential numbers and repeat them

Suppose, you have been given the task to generate a sequence of numbers and repeat them. For example - 1,2,3,4,1,2,3,4,1,2,3,4.
You can use the below formula and drag down

  =MOD(ROWS($1:1)-1,4)+1

Replace 4 with with any other number to generate any other sequence. Hence, if you want to generate 1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10 then formula becomes

  =MOD(ROWS($1:1)-1,10)+1

  =MOD(ROWS($1:1)-1,X)+Y
  X - Number of numbers
  Y - Starting Number

Utilizing above formula, you want to generate the sequence 5,6,7,8,9,10,5,6,7,8,9,10,5,6,7,8,9,10, then use below formula (You need 6 numbers and stating number is 5)

  =MOD(ROWS($1:1)-1,6)+5

1.16 Repeat a Number; Increment and Repeat

Suppose, you have been given the task of repeating a number and increment that number and repeat it. For example - 1,1,1,1,2,2,2,2,3,3,3,3..... (Here, we are repeating it 4 times and incrementing and repeating 4 times again and so on)

  =ROUNDUP(ROWS($1:1)/4,0)

  =ROUNDUP(ROWS($1:1)/4,0)+4

  =ROUNDUP(ROWS($1:1)/X,0)+Y-1
  X - Number of times a particular number is repeated
  Y - Starting Numbers

Hence, if you want to start with number 7 and you want to repeat it 5 times, then following formula should be used

  =ROUNDUP(ROWS($1:1)/5,0)+6

1.17 Generate random Numbers that does not repeat

Suppose, you want to generate non-repeating random numbers between 1 to 30, you can use following formula in A2 and drag down

  =IFERROR(AGGREGATE(14,6,ROW($1:$30)*NOT(COUNTIF($A$1:$A1, ROW($1:$30))),
  RANDBETWEEN(1,30-ROWS($1:1)+1)),"")

Note: $A$1:$A1 is with reference to A2 as you put formula in A2 and dragged down. Suppose, you had put the formula in G4, this should be replaced with $G$3:$G3. If your starting and ending numbers are in B1 and C1, use below formula

  =IFERROR(AGGREGATE(14,6,ROW(INDIRECT($B$1&":"&$C$1))*
  NOT(COUNTIF($A$1:$A1,ROW(INDIRECT($B$1&":"&$C$1)))),
  RANDBETWEEN($B$1,$C$1-ROWS($1:1)+1)),"")

For versions, prior to 2010 following basic construct can be used (Build error handling depending upon the version. For example, Excel 2007 will support IFERROR whereas 2003 supports ISERROR)

  =LARGE(INDEX(ROW($1:$30)*NOT(COUNTIF($A$1:$A1, ROW($1:$30))),,),
  RANDBETWEEN(1,30-ROW(A1)+1))