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: