Log In to continue learning
In the previous videos, you used conditional formulas including COUNTIF, SUMIF, and AVERAGEIF. These only check one condition. What if you wanted to calculate based on multiple conditions, like comparing counts based on cancellation status and market segment? Let's take a look. For market segments, I'm not sure at a glance how many there are. Use the UNIQUE formula to identify all of the unique categories in the market segment column. But I don't want to select the header column, so I'm just going to start this data range from M2 onwards. You can see now that I got all of the unique categories for market segment, and I just applied some formatting. I'm also going to add a total row down below. So now in this first cell, I want to count the number of cancelled bookings if the market segment is also offline. Use the COUNTIFS function. Let's take a quick look at the help. So if you get stuck, you can always go here. It will show an example of how to utilize this function. Go to the data and select the booking status column, and I want to include only the rows that are cancelled. Select the market segment. In this case, rather than typing offline, I'm going to select the cell. A22 has the value offline. This will make it easier when I copy the formulas later. Good work. So 3,153 are cancelled and offline. Notice that A22 is a relative cell reference. If you use the fill handle, you can calculate all the counts for each market segment. The fill handle allows you to copy a formula to many cells, and relative cell references will update. So I'm going to take the fill handle and drag it down, and that will update these counts for each category. Great. Most of the cancellations occur with online reservations, then offline. Otherwise, cancellations are pretty rare across the other categories. I accidentally included the total row here. I'm just going to delete that. Okay. Now let's calculate the total number of bookings. So here I'm going to do a sum. You can see that it detected what I wanted to do. In this case, it's correct. I want to sum all of the rows above in the table, so I can just hit tab here and complete. The total cancelled reservations equals the total that we calculated previously. Let's calculate the percentage. So here I'm going to take equals, the cancelled count for offline, and divide by the total, and then I'm going to change the format to percent. Can we use the fill handle here? Let's try it out. You'll notice that I get the divide by zero error for each of the rows below. Something must be wrong, so let's investigate. Let's take a look at the online. You can see here that the formula is trying to divide the cancelled count for online by the blank cell below. You'll notice that when I applied the fill handler downward, both references moved, but we only want to move the cell reference for the first entry. We want the second one to stay the same. So what we actually want is to divide by the total, so I'm going to clear this cell out, and then we want to add an absolute reference to the formula. You can use the dollar sign to prevent a row or column reference from changing. In this case, I want to stop the row 27 from changing, so I'm going to add a dollar sign in front of 27, which will keep that reference fixed. So now when I fill this formula down the rows, you will see that each cell references the cell where our total is contained. This is a little bit of an unusual notation. It might take a little bit of time to get used to. So for example, if you want the row to stay fixed, you would put the dollar in front of the row number. If you want the column to stay fixed, you would put the dollar sign in front of the column, and if you want the whole cell to be fixed, you can put a dollar in front of both the row and the column. You can also calculate our total sum, which again in the text, so I'm going to tab to apply it. I'll do the same quickly for not cancelled. You can try this on your own, but I just want to do a quick comparison. So offline, corporate, and complementary bookings make up a greater proportion of not cancelled bookings compared with cancelled bookings. Now let's say I want to investigate the total revenue for all of the bookings in the data set, again across different categories of market segments, and based on whether it was cancelled or not. So I've added a new column here called total value, which is calculated by the average price per room times the number of weekdays plus the number of weekend days. So now we want to use the SUMIFS function to total up the total revenue associated with different booking statuses and market segments. So let's start again with the cancelled reservations for the offline category. Write the SUMIFS function, open parentheses. For the sum range, I'm going to select the total value. For the criteria range, we want to evaluate the booking status. For the criteria, I want it to be equal to the cancelled cell. So in this case, I want the row name to be fixed, so I'm going to add a dollar sign in front of the 29. And then I want to add my next condition, which is to check the market segment, which is going to be column M, and I want to compare that to the category to the left. Then lastly, close the parentheses, hit return. So you can see the total revenue for cancelled offline bookings is about 900,000 euros. So let's drag this formula down and see what results we get. So similar to the count data, you can see that we have the highest number for the online category, but the categories below are much smaller. Let's calculate the sum on these results, and then let's format all these results in euros. That's a lot of potential revenue, more than 4 million euros. How about for not cancelled bookings? The only difference in these formulas is that they're going to reference not cancelled for booking status. Now you can do all sorts of analyses and business predictions and planning given these numbers. Notice the difference between these results. Overall, there's over 7 million euros in not cancelled reservations, but compared to the 4 million, there's a lot more revenue that was left on the table from these bookings. You saw that COUNTIFS and SUMIFS give you a ton of flexibility. In the practice lab for this lesson, you will calculate the revenue per booking, not just total revenue. Remember, if you forget about which parameters the function uses, you can always check the help menu. You're almost done with this lesson. Follow me to the next video to learn about data processing techniques and spreadsheets.