Hello everyone. This video we talk about one special trick of some ifs formula. And I would seriously advise you that you must go through our earlier videos on some IDs to be able to fully appreciate the flavor and the magic of the upcoming trick. Now, you notice I have couple of purchase orders in column A said 001, c 002, and 003. So, basically three purchase orders which are repeating multiple times. Now the amount, the amount relevant amount is also given in column B.

Now, you will notice how column C numbers is progressing. You notice 001 that purchase order first purchase order shows 80,000. But when it shows the next time in my list, the third column gives me a total cumulative differential cumulative total 80,000 plus 55,000 and that is 130 5000. If it appears once more in the list It shows me the cumulative total as 80,000 plus 55 plus 70. And that's 205 thousand. Now I have seen law professionals using a basic formula under sum, and that gives me a simple cumulative total, I will first show you that I'm choosing the base number, putting a colon automatically the same cell reference repeats itself, I will close the bracket.

In fact, before I press enter, I must choose the starting point of this pre selection. Starting point of this range selections, I press f4. Now be careful I have not fixed and locked the second part of the table array or the range that you may have chosen. What it does, as you copy paste at every copy pasted cell, the last part of the table range or the range that you have chosen is continuing to change. And that makes the entire area of the selection variable. As you can notice from the blue borders, it's increasing at every But the point here is it is cumulatively adding up all the numbers irrespective of which p o does it belong to our target is different.

Our target is that it must also consider the uniqueness of the purchase order to be able to compute the cumulative total. And this is where the sum ifs formula will come into the picture. Let's see how this is achieved. I write equals sum ifs, pressing TAB key going to the effects button. And this is where our story begins. In the criteria one, the third box, I go ahead and choose the purchase order number right from the beginning.

And then I step inside criteria range one, I go to the same cell. I go to the same cell, I choose it, I put a colon and automatically the same cell reference replicates itself. You might tell me, this doesn't look like rain, but let me tell you, this is technically a range all the pointing to just one cell but that colon the replication of the sale makes it a proper range. Now the trick as we saw earlier in the sum formula is to ensure the starting point of the range is fixed. Now eventually what is going to happen as I copy paste the formula, the first part A for remains same, but the second part or the ending part is going to extend to a five a six a seven, thus making the range variable it'll include more values, more purchase orders as it is vertically copy pasted down.

It also discussed one phenomenon while discussing some range that the height of the ranges should be seen. It should not be different. So I go ahead I pick up the amount Are you the same strategy as I used in column A, I put Kowloon automatically repeat replicates, automatically replicates once again I choose the starting point I fixed that. Be careful, the height should be same, and the first part should be locked, not the second as I press OK. Copy Paste the formula down, you better understand the answer as I help you understand through the colored borders notice, when I double click on the first formula first occurrence of this formula, it looks like you're looking for only one variable one criteria in that one single cell range. Of course, it's appearing only once, and that reason you're getting 80,000 But wait, when you're looking for the second Busey z 002, you notice it is appearing only once in the red colored border.

And that is a reason it picks up only once the amount and we get one lakh. But when we copy paste to the next cell, since 002, appears two times in this red colored border which is increasing the range which is increasing, it is picking up two values that is one lakh and 40,000, not the 80,001 and thus you get 140 thousand. In fact, have you written CV 002 first time, notice the answer changing 80,000 which is this ad plus one lakh. This is one lakh 80,000 appearing here, the one which is blinking 180 plus 40 that makes it how much 220 and that's what it is here. So, all in all, the trick that we have used is differential cumulative total, ensuring that the height of the ranges that you have chosen remains the same and the starting point is fixed starting point of the range.

Apply this and this could be used in multiple scenarios where you need the running total you want to see whether the amount sanction under purchase order has been fully exhausted.