Get a month of TabletWise Pro for free! Click here to redeem 
TabletWise.com
 

SUMIFS(): Conditional Summation (2 Criteria)

Software Microsoft Excel Advanced Excel Crash Course Section 11: Lookup and Conditional Calculations
4 minutes
Share the link to this page
Copied
  Completed
You need to purchase the class to view this lesson.
One-time Purchase
$99.99
List Price:  $139.99
You save:  $40
د.إ367.26
List Price:  د.إ514.18
You save:  د.إ146.92
A$144.05
List Price:  A$201.68
You save:  A$57.62
৳8,483.46
List Price:  ৳11,877.18
You save:  ৳3,393.72
CA$135.59
List Price:  CA$189.83
You save:  CA$54.24
CHF 94.49
List Price:  CHF 132.29
You save:  CHF 37.80
kr662.80
List Price:  kr927.95
You save:  kr265.14
€88.94
List Price:  €124.52
You save:  €35.58
£80.18
List Price:  £112.26
You save:  £32.07
HK$774.92
List Price:  HK$1,084.92
You save:  HK$310
₹7,463.22
List Price:  ₹10,448.81
You save:  ₹2,985.59
RM428.70
List Price:  RM600.20
You save:  RM171.50
₦38,796.12
List Price:  ₦54,316.12
You save:  ₦15,520
kr949.12
List Price:  kr1,328.81
You save:  kr379.68
NZ$153.04
List Price:  NZ$214.27
You save:  NZ$61.22
₱4,948.18
List Price:  ₱6,927.65
You save:  ₱1,979.47
₨16,755.82
List Price:  ₨23,458.82
You save:  ₨6,703
S$139.39
List Price:  S$195.16
You save:  S$55.76
฿3,111.68
List Price:  ฿4,356.48
You save:  ฿1,244.80
₺686.46
List Price:  ₺961.07
You save:  ₺274.61
R1,703.77
List Price:  R2,385.35
You save:  R681.57
Already have an account? Log In

Transcript

Hi there. Are you ready for multiple criteria based some ifs formula? If yes, then let's proceed. If you've watched one of our previous videos, which talks about the basic introductory discussion on some ifs formula, then you will enjoy this. Sure. The next question that I would want to address is in case column A has the party name, column B has the payment mode and through various modes of payment, it has given me the payment.

Now the question is DCs in EFT how much payment received by this mode through this account. Now as I write IBM Naft, I should get an answer which pertains to IBM st amounts only. Let me underline those so that we can pinpoint and see what the answer is looking like so 20 plus hundred and that is C 20. Now how do we do that? Let's see. equal to some if s one of the areas that people may Mistake at the outset is they use some formula.

No, this can only be solved through some IDs. I go to some ifs formula, I click on effects. And now the task is to press TAB key TAB key multiple times so that the various tick boxes open up those are the parameters for some is to set the input parameters for summit's. Now, as we had discussed earlier that these criteria comes in pair. That is, for one criteria, there's a specific defined range. For the next criteria.

There's another specific defined range. Now make sure that when you're picking up a criteria, let's say PCs, you should pick up the corresponding range in the criteria range one, which is column A portion, but do not do so in criteria range two, because that would be inconsistent. And subsequently, we'll also see there's a scroll bar on the right hand side which if you drag down, you get to see there are 127 criteria, which you can build in the formula. Now that's fantastic. All I've never used Personally, but you have the option at maximal use up to six to seven criteria. So we start with the problem criteria one.

Ideally I should be starting from some range, but logically, you should start from criteria one because that's easier for our brain to understand criteria one, here goes DCs, then, let me jump to the previous box criteria range one, I'll be careful with that. And I choose from eight till a 15. I'm going to press f4 to fix and lock the range. So one pair complete. Now let me press TAB key TAB key and once again, so so that I get to each two criteria to I go to criteria two, and I pick up any st from the input cell, not from the data from the input cell. Let me press Shift Tab key that will make me jump to the first text box right above it.

Now, criteria range two, this is where I'm going to choose the next range. I'll be careful choosing the range, I make sure that I be consistent in selection, Visa v the criteria range one, it cannot be a scenario where you are choosing eight to 15 for one range, and seven to 15 for another. So I choose the correct criteria range, and immediately I press f4. So two pairs completed. Now it's the final turn of some range. So I place my cursor on the first box, and here I go.

Some range is the range where all numbers are present available for submission. I press f4 to fix and lock and if you're correct, then you should get the answer right away under this preview sample. Okay. 130 5000. And in fact, if I just want to test this scenario with Zenith, Zenith NFT in total 26,000 and that's what I get 26,000 so it is not necessary that you should place the criteria one isn't it, it could have also been Naft as a force criteria, but make sure that if you're choosing criteria one as any FP, the corresponding green should also be column B. That's all we want.

And this is the intermediary level of Formula some ifs

Sign Up