Essential Excel Functions for Data Analysis: CONCATENATE, LEN, COUNTA, DAYS, NETWORKDAYS, SUMIFS, VLOOKUP, and More

The guide presents a collection of powerful Excel formulas that are indispensable for data analysts, covering text manipulation, counting, date calculations, conditional aggregation, lookup, error handling, and statistical analysis.

CONCATENATE

Combines the contents of multiple cells into a single string, useful for creating API endpoints, product SKUs, or Java queries.

Formula: =CONCATENATE(selected_cells)

Example: =CONCATENATE(A2,B2)

LEN

Returns the number of characters in a cell, helpful for measuring SKU lengths or other identifiers.

Formula: =LEN(cell)

Example: =LEN(A2)

COUNTA

Counts non‑empty cells, allowing analysts to assess data completeness.

Formula: =COUNTA(range)

Example: =COUNTA(A10)

DAYS / NETWORKDAYS

DAYS calculates calendar days between two dates; NETWORKDAYS calculates working days, optionally excluding holidays.

Formulas:

=DAYS(end_date,start_date)

=NETWORKDAYS(start_date,end_date,[holidays])

SUMIFS

Aggregates values based on multiple criteria, extending the basic SUM function.

Formula: =SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2]…)

Example: =SUMIF($B$2:$B$28,$A$2:$A$28,$F2)

AVERAGEIFS

Calculates the average of a range that meets one or more conditions.

Formula: =AVERAGEIFS(average_range,criteria_range1,criteria1,…)

Example: =AVERAGEIF($C:$C,$A:$A,$F2)

VLOOKUP

Searches for a value in the first column of a table and returns a value from a specified column.

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

Example: =VLOOKUP($A2,$G$1:$H$5,2,0)

FIND / SEARCH

Locates a substring within a text string; FIND is case‑sensitive, SEARCH is not.

Formulas:

=FIND(text,within_text,[start_num])

=SEARCH(text,within_text,[start_num])

IFERROR

Replaces error values (e.g., #VALUE!) with a specified result, keeping worksheets clean.

Formula: =IFERROR(value, value_if_error)

Example: =IFERROR(FIND("BIG",A6,1),"")

COUNTIFS

Counts the number of rows that meet multiple criteria.

Formula: =COUNTIFS(range1,criteria1,[range2,criteria2]…)

Example: =COUNTIFS($A:$A,$F9)

LEFT / RIGHT

Extracts a specified number of characters from the start ( LEFT ) or end ( RIGHT ) of a text string.

Formulas:

=LEFT(text,num_chars)

=RIGHT(text,num_chars)

Examples: =LEFT(A6,3) and =RIGHT(A6,4)

RANK

Ranks a value within a list, optionally in ascending or descending order.

Formula: =RANK(number,ref,[order])

Example: =RANK($B7,$B$2:$B$7,0) (0 returns descending rank).

MINIFS / MAXIFS

Return the minimum or maximum value that meets one or more criteria.

Formulas:

=MINIFS(min_range,criteria_range1,criteria1,…)

=MAXIFS(max_range,criteria_range1,criteria1,…)

Examples: =MINIFS($B$B,$A:$A,$E5) and =MAXIFS($B$B,$A:$A,$E5)

SUMPRODUCT

Multiplies corresponding components in two or more arrays and returns the sum, useful for weighted averages and profit calculations.

Formula: =SUMPRODUCT(array1,array2,…)/cell

Example: =SUMPRODUCT(B2:B9,C2:C9)/C10

The article concludes with a QR code offering free Python learning resources, but the core content remains a practical reference for Excel data‑analysis functions.