Excel Formulas

EXCEL FORMULAS

1. SUM

Formula: =SUM(5, 5) or =SUM(A1, B1) or =SUM(A1:B5)

2. COUNT

Formula: =COUNT(A1:A10)

3. COUNTA

Formula: =COUNTA(A1:A10)

4. LEN

Formula: =LEN(A1)

5. TRIM

Formula: =TRIM(A1)

6. RIGHT, LEFT, MID

Formulas: = RIGHT(text, number of characters), =LEFT(text, number of characters), =MID(text, start number, number of characters).

(Note: In all of these formulas, wherever it says “text” you can use a cell reference as well)

7. VLOOKUP

Formula: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

8. IF Statements

Formula: =IF(logical_statement, return this if logical statement is true, return this if logical statement is false)

9. SUMIF, COUNTIF, AVERAGEIF

Formulas: =SUMIF(range, criteria, sum_range), =COUNTIF(range, criteria), =AVERAGEIF(range, criteria, average_range)

10. Manually enter Excel formulas:

Long Lists: =SUM(B4:B13)

11.  Click the Insert Function button

=COUNT(B4:B13) Counts the numbers in a range (ignores blank/empty cells).

12  Select a function from a group (Formulas tab)

=TODAY() Inserts today’s date.

13. The Recently Used button

=AVERAGE(B4:B13) adds the list, divides by the number of values, then provides the average.

14.  Auto functions under the AutoSum button

=MAX(B4:B13) returns the highest value in the list.

=MIN(B4:B13) returns the lowest value in the list.

15. =DAYS

Formula: =DAYS(A30,A29)

16. =NETWORKDAYS

Formula: =NETWORKDAYS(A33,A34)

17. =TRIM

Formula: =TRIM(A39)

18. =CONCATENATE

Formula: For dates enter: =CONCATENATE(E33,” “,F33,”, “,G33)

Formula: For phone numbers enter: =CONCATENATE(E37,”-“,F37,”-“,G37)

19. =DATEVALUE

Formula: =DATEVALUE(H33)

20. Count cells not equal to x or y

Formulas: COUNTIFS. SUMPRODUCT

COUNTIFSSUMPRODUCT

 Resources:

https://exceljet.net/formulas

https://support.office.com/en-us/article/Overview-of-formulas-in-Excel-ECFDC708-9162-49E8-B993-C311F47CA173