Subscribe my youtube channel!

Hello,

Today I’m gonna talk about 3 math function used in Excel, one that simply makes the addition of values (SUM), and 2 others that make addition of values using 1 criteria (SUMIF) or 2 or more criteria (SUMIFS).

Lets start with the data table to work.

This table show the number os rooms booked in 3 differents hotel (for 4 different months):

The first operation that i’m going to do is get the total of rooms booked in this 4 months using the 3 hotels:

To make this operation, i just have to sum all the values, using the function **SUM.** This function receive in parameters just sigle cells or cell ranges (in this case receives cell range D3:D14).

Final result is (7465 booked in 4 months):

In the second example, using the same table, i want to return number of booked rooms (cell B20), for a specific hotel (cell A20):

Now i have to make a sum, but with the name of hotel as condition (sum all the values that match with value of cell B20):

To make sum depending on a condition we use **SUMIF**. This function has 3 parameters, first the **range** wich you will apply your **criteria, **and finally the **sum_range **that cantain the value to sum. In the example, the range is the cell range **C3:C14**, criteria for example could be **Hotel Madrid **and **sum_range **is cell range D1:D14.

The final result is:

In the final example i make an exercise but using 2 conditions (use **SUMIFS** when using more than 1 condition).I want to return the number of booked rooms depending the hotel name (A23) and the month (B23):

First parameter of **SUMIFS** is **sum_range**, that in our example is the range that have all the values of booked rooms (D3:D14). The others parameters are the diferent criteria ranges with the respective criteria value. In this example i have 2 ranges with 2 criterias (hotel name and month). First **criteria_range** is Hotel names (C3:C14)and the cell where i specifie the hotel name (A23) is my first **criteria**.The second **criteria_range** is the months (B3:B14) and the cell B23 where i specifie the month is my second **criteria** value:

Final result (selection one hotel and one month) is number of booked rooms for **Hotel Madrid** in **January** Month:

### Like this:

Like Loading...

It can we do by using the vlookup, can’t it?

LikeLike

Hello, Vlookup is not a function to make arithmetic operation. In the three function that i show, the only one that is possible to do with vlookup is the IFS function because you have only a raw with condition HOTEL MADRID/JANUARY (RAW4). VLOOKUP in the other cases see multiple raws with same value what is impossible with this function. Even this, using IFS you have to concatenate comumn B and C to have the information of MONTH and HOTEL together in same column (VLOOKUP just look a unique column value -lookup_value parameter). SUMIFS its the right function to this 🙂 Thanks

LikeLike

Educational

LikeLike

Thanks:) Nestor

LikeLike

Good information and the graphics were clean and easy to follow. I agree with the above comment as well in that SUMIFs is the formula you want to use here instead of a vlookup as the vlookup would only show you the first value in the list

LikeLike

Heloo TylerG. Thanks for your comment:)

LikeLike