Use of function SUM/SUMIF/SUMIFS in excel

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):

excel-table

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

excel-table_sum

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):

excel_sum_1

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

excel_sumif_1

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):

excel_sumif_2

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:

excel_sumif4

excel_sumif_3

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):

excel_sumifs1

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:

excel_sumifs2.png

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

excel_sum_ifs3.png

escel_sum_ifs2

 

Advertisements

4 thoughts on “Use of function SUM/SUMIF/SUMIFS in excel

    1. 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

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s