Three Functions for Excel...lence

Three Functions for Excel…lence

Insight from Exceljet

Ready to nerd out? Good. Same.

Here are three formulas:

SUMIF

You need data to adjust departmental workflows or reallocate resources.

The easiest way to get that data is to use Excel’s SUMIF function. SUMIF will add only the values that match the specified criteria.

Here’s an example: If your spreadsheet includes employee name, department, projects completed, and hours worked.

To sum all hours worked by the Ops department, you need three data points for the formula:

• The “Department” column (B2:B7)

• The department you want to sum the data for is “Ops”.

• The “Hours Worked” column (D2:D7)

Combine it in the formula: =SUMIF(B2:B7, "Ops", D2:D7).

SUMIF does all the sorting and adding for you.

(The answer is 83, you dork).

CONCATENATE

We use the CONCATENATE function to join text from two columns.

An example:

We have our “Firstname” data in Column A, and our “Lastname” data in Column B.

There is one condition!

For accurate output, include a space between quotation marks, like this: “ “.

Our completed function is: =CONCATENATE(A2, B2).

VLOOKUP

Time to go deeper.

If you don’t know how to use VLOOKUP, this will be valuable.

Like the others, it’s explained best with an example:

We have a table with IDs, Names, Sales, and Regions (Columns A-D).

We need to populate Column D with the correct “Region” data from a different table on a different sheet.

We can do this in two ways:

• We will manually go through the list of names and copy/paste the region data to the correct name.

• We can use ✨ VLOOKUP ✨

Let’s use VLOOKUP.

Here’s what we need:

• We’ll use the cell (B2) to lookup the data using names.

• The table we’re searching through (second table in columns G-H)

• Which column contains the information we want? (Count the columns in the G-H table until you reach the “Region” column. In this case, it’s 2)

• What type of lookup are we performing? (Choose “TRUE” or “FALSE”. For an exact match, like in this case, choose FALSE)

Following those steps, we get our completed function:

=VLOOKUP(B2, G:H, 2, FALSE)

What does the table look like when we drag that formula down?

It’s like magic (or I’m just a nerd).