Excel Formulas Bible
Here B2:B20>14 is like a criterion in COUNTIF (=COUNTIF(B2:B20,">14"))
24. 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.
25. Extract First Name from Full Name
=LEFT(A1,FIND(" ",A1&" ")-1)
26. Extract Last Name from Full Name
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))
27. 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.
28. 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))
29. 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),""),"")
© eforexcel.com Page 7 of 38
Excel Formulas Bible
30. 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.
31. First Day of the Month for a Given Date
Suppose you have been given a date say 10/22/14 (MM/DD/YY) and you want to calculate
the first day of the Current Month. Hence, you want to achieve a result of 10/1/2014
(MM/DD/YY).
The formulas to be used -
=DATE(YEAR(A1),MONTH(A1),1)
=A1-DAY(A1)+1
=EOMONTH(A1,-1)+1
32. How Many Mondays or any other Day of the Week
between 2 Dates
Suppose A1 = 23-Jan-16 and A2 = 10-Nov-16. To find number of Mondays between these
two dates
© eforexcel.com Page 8 of 38
Excel Formulas Bible
=SUMPRODUCT(--(TEXT(ROW(INDIRECT(A1&":"&A2)),"ddd")="Mon"))
“Mon” can be replaced with any other day of the week as per need.
33. Maximum Times a Particular Entry Appears
Consecutively
Suppose, we want to count maximum times “A” appears consecutively, you may use
following Array formula -
=MAX(FREQUENCY(IF(A2:A20="A",ROW(A2:A20)),IF(A2:A20<>"A",ROW(A2:A20))))
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.
34. Find the Next Week of the Day
There are 2 scenarios in this case. For example, if today’s date is 2-Jan-17 (Monday) and I
try to find the next Monday, I can get either 2-Jan-17 or 9-Jan-17 as per need. For Tuesday
to Sunday, it is not a problem as they come after 2-Jan-17 only.
Case 1 - If the Day falls on the same date, then that very date (Hence, in case of 2-Jan-
17, next Monday would be 2-Jan-17 only)
Next Mon =CEILING($A$1-2,7)+2
Next Tue =CEILING($A$1-3,7)+3
Next Wed =CEILING($A$1-4,7)+4
© eforexcel.com Page 9 of 38
Excel Formulas Bible
Next Thu =CEILING($A$1-5,7)+5
Next Fri =CEILING($A$1-6,7)+6
Next Sat =CEILING($A$1-7,7)+7
Next Sun =CEILING($A$1-8,7)+8
Case 2 - If the Day falls on the same date, then next date (Hence, in case of 2-Jan-17, next
Monday would be 9-Jan-17 only)
Next Mon =CEILING($A$1-1,7)+2
Next Tue =CEILING($A$1-2,7)+3
Next Wed =CEILING($A$1-3,7)+4
Next Thu =CEILING($A$1-4,7)+5
Next Fri =CEILING($A$1-5,7)+6
Next Sat =CEILING($A$1-6,7)+7
Next Sun =CEILING($A$1-7,7)+8
35. Find the Previous Week of the Day
There are 2 scenarios in this case. For example, if today’s date is 2-Jan-17 (Monday) and I
try to find the previous Monday, I can get either 2-Jan-17 or 26-Dec-16 as per need. For
Tuesday to Sunday, it is not a problem as they come prior to 2-Jan-17 only.
Case 1 - If the Day falls on the same date, then that very date (Hence, in case of 2-Jan-
17, previous Monday would be 2-Jan-17 only)
Previous Mon =CEILING($A$1-8,7)+2
Previous Tue =CEILING($A$1-9,7)+3
Previous Wed =CEILING($A$1-10,7)+4
Previous Thu =CEILING($A$1-11,7)+5
Previous Fri =CEILING($A$1-12,7)+6
Previous Sat =CEILING($A$1-13,7)+7
Previous Sun =CEILING($A$1-14,7)+8
Case 2 - If the Day falls on the same date, then previous date (Hence, in case of 2-Jan-
17, previous Monday would be 26-Dec-16 only)
Previous Mon =CEILING($A$1-9,7)+2
Previous Tue =CEILING($A$1-10,7)+3
Previous Wed =CEILING($A$1-11,7)+4
Previous Thu =CEILING($A$1-12,7)+5
Previous Fri =CEILING($A$1-13,7)+6
Previous Sat =CEILING($A$1-14,7)+7
Previous Sun =CEILING($A$1-15,7)+8
36. Get File Name through Formula
Before getting this, make sure that you file has been saved at least once as this formula is
dependent upon the file path name which can be pulled out by CELL function only if file has
been saved at least once.
© eforexcel.com Page 10 of 38
Excel Formulas Bible
=CELL("filename",$A$1)
37. Get Workbook Name through Formula
Before getting this, make sure that you file has been saved at least once as this formula is
dependent upon the file path name which can be pulled out by CELL function only if file has
been saved at least once.
=REPLACE(LEFT(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))-
1),1,FIND("[",CELL("filename",$A$1)),"")
38. Get Sheet Name through Formula
Before getting this, make sure that you file has been saved at least once as this formula is
dependent upon the file path name which can be pulled out by CELL function only if file has
been saved at least once.
Use following formula -
=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")
Make sure that A1 is used in the formula. If it is not used, it will extract sheet name for the
last active sheet which may not be one which we want.
If you want the sheet name for last active sheet only, then formula would become
=REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")
39. Get Workbook's Directory from Formula
Before getting this, make sure that you file has been saved at least once as this formula is
dependent upon the file path name which can be pulled out by CELL function only if file has
been saved at least once.
If your workbook is located in say C:\Excel\MyDocs, the formula to retrieve the directory
for this would be
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-2)
40. Last Day of the Month for a Given Date
Suppose, you are given a date say 10/22/14 (MM/DD/YY) and we want to have the last
date of the month for the given date. Hence, you needs an answer of 10/31/14. The formulas
to be used in this case -
=EOMONTH(A1,0)
=DATE(YEAR(A1),MONTH(A1)+1,0)
© eforexcel.com Page 11 of 38
