Hello everybody. Today we will talk about the formula sum ifs. Now it's one of the most underutilized formula especially with the replacement of some ifs. Especially with the replacement of some ifs formula with the more powerful formula some if s an extra s or multiple criteria. So you start with a discussion with says, column A has the party name or the client. It has made me payment over a period of time through various modes, online transfer and EFT or through cheque or via cash.
Now to start with, what I want is in the cell which I've colored as yellow, it is one of the input cells. It says whenever the name is TCS, all the payments, which you have received against TCS in total, that should be given as summation. So 80,000 plus 55,000 plus 70,000. All these three values should be a part of my art In this blank cell, so we start with a formula some ifs. Now, I am sure that some of you just one of those few would be wondering why are we not using the VLOOKUP at this point? Well, if we are suppose Well, if he had used v lookup lookup value would have been TPS.
And the table array would have inevitably been this entire data array table array, which I'm going to fix f4 comma, column index number 123. Third is where the answer lies, and false for an exact match. Now, this will only give you the first instance of PCs, not the total. And that's the reason why we are using summit's in the first place. So some ifs, pressing the tab key clicking on effects and you get the function argument. Now the small small bug in Microsoft Excel some a formula unless you click on At the outset, you will be presented with two input boxes.
But mind you, as you click on the second one, the third appears a third coat appears fourth, and fifth appears. And you can also move across these text boxes by pressing TAB key or pressing Shift Tab key in case you want to make the cursor move in the upper direction. So now we focus on the criteria one. Notice they come in pairs criteria one and criteria range one. The logic behind them is that your one single cell value or single cell criteria is the cell which is marked in dark yellow. That's the criteria.
Now, I would be taking the value from the PCs on the above here, not one below because tomorrow if I change this to IBM, I want a revised answer. So criteria being TCS, I go to the next box, and the next box above it, which is Shift Tab key will take me there and then I choose the indoor range where I can find TCS values. Now, you can take with header you can take without header, but ensure that you keep this range consistent in case you go ahead and choose the sum range in future, which we'll see right now. So at this point in time, I will press f4. This is to ensure that if at all I feel the need to copy paste the formula down the range of values the range, the criteria range doesn't shift down along with it. Then I press Shift Tab key wherein my cursor is reached on where am I my cursor is taken to the first box, some range and Nick and make sure that you are choosing the range of values which needs to be summed up and that to inconsistency with the criteria range, which means if you choose the range one more than what you had taken four criteria range, let's say from eight to 15.
This time, if I'm taking seven To 15 all I press f4 I still don't get the answer in the sample preview. So I make sure I make that amendment. I start with eight. So now, a couple of observations before I press enter, I'm getting the answer to 5000. Then f4 has been applied to go these ranges and both are starting and ending at the same level. As I press OK. Notice I get to 05 thousand.
And if I change the value to IBM, answer changes. What and if I change the value to IBM, answer changes, and that's what we want. In the next video, we'll also see how to cater to two variables. For example, I don't want to find out an answer which says TCS, all payment to 05 thousand I only want to find out TCS, any ft 80,000 TCS, any sp 55 combination of them is 130 5000 This is two variables summits which we will discuss in the next video.