সীমাবদ্ধতা
Saturday, May 14, 2022
যারা ড্রাইভিং লাইসেন্স করতে চাচ্ছেন!
Saturday, April 23, 2022
পৃথিবী ও সূর্যের দূরত্বের কারণেই পৃথিবীতে প্রাণ সৃষ্টির জন্য অত্যানুকূল পরিবেশ সৃষ্টি হয়েছিলো!
Sunday, March 20, 2022
বস্তুর ভরের সৃষ্টি হলো কেন এবং কিভাবে?
Thursday, February 10, 2022
🔷 পৃথিবী সূর্যকে কেন্দ্র করে ঘুরে না? 🔷
Tuesday, January 4, 2022
কসমোলজিঃ- মহাবিশ্বের সৃষ্টির রহস্য।
Saturday, December 25, 2021
সৈয়দপুরের সেই অভিশপ্ত ট্রেন!
Saturday, December 18, 2021
❝ যে-কারণে পৃথিবী নয়, সূর্যই পৃথিবীর চারদিকে ঘোরে ❞
এই বাচ্চা ছেলেটার নাম আদৌ জানা যায়নি।
Monday, November 29, 2021
টাইম ট্রাভেল মেশিন। গানিতিক সমীকরণ কি বলে?
Wednesday, November 24, 2021
Excel SUMIF with VLOOKUP: Formula Examples.
#1: Excel SUMIF with VLOOKUP for looking up the criteria value
Suppose you have the following spreadsheet that contains Orders and Products data in two separate tables. Then, you want to add up the amount for Firecracker and put the result in I3.
But, as you can see, the Orders table does not have a column for product names. This means that you can’t just sum up the amount for your orders where the product name is equal to Firecracker directly with the SUMIF function.
The solution? You need to get the product number of Firecracker first, then use it as the criteria in your SUMIF function. Here are the steps:
Step 1: Write the VLOOKUP formula in I3 to get the product number of Firecracker.
The formula looks for a value that exactly matches “Firecracker” in the first column of the range E3:F10. Then, it returns “SF706” from the second column of the range (column F).
Step 2: Use the VLOOKUP in a SUMIF, as shown below:
The SUMIF formula adds the amount in C3:C14 where any value in B3:B14 equals “SF706“. You can see the final result in I3, which is $400.
#2: Excel VLOOKUP with SUMIFS to lookup with multiple criteria
For this example, we use a small subset of an Employee dataset stored in Airtable. We exported data from Airtable to Excel because, as you know, Microsoft Excel is one of the most convenient tools for data analysis. We manage the process automatically using Coupler.io, an integration tool for exporting data from different sources to Excel. With Coupler.io, anyone can set up seamless Microsoft Excel integrations with other apps, such as Airtable, Jira, Shopify, Slack, and many more!
Now, with the following Employee data, suppose you want to find an employee’s email address based on their first name, last name, and department.
Assume that each Employee ID value is unique, and each department does not contain any employees with the same full name.
It would be easy if you only need to lookup based on the Employee ID. You could just use VLOOKUP, and it’s done. But unfortunately, you can’t use VLOOKUP because you need to do a lookup on multiple columns.
So, what’s the solution? Well, luckily, you can combine both Excel VLOOKUP and SUMIFS to get the result you want! Use SUMIFS to get the ID of the specified employee based on their first name, last name, and department. Then, use that ID in a VLOOKUP function to return the email address.
Here are the steps:
Step 1: Use SUMIFS to get the ID of the specified employee:
As you can see, the above formula in H4 returns the ID of Janie Jones from the Engineering department, which is 113004.
Step 2: Use the SUMIFS within a VLOOKUP to find an email address based on the employee ID, as shown below:
The VLOOKUP formula above uses the result of the SUMIFS function as the lookup value. It then finds the exact match in range A2:E10 and returns “jjones@keizztrr.com“, which is located in the fifth column of the range.
#3: Excel SUMIF + SUMPRODUCT + VLOOKUP to sum values across multiple sheets
Suppose you have the following spreadsheet with four worksheets: Summary, Apr, May, and Jun. In the Summary worksheet, you want to calculate the quarterly bonus for each salesperson using the lookup table on the right.
In this scenario, you will sum up the sales across Apr, May, and Jun for each salesperson. With these numbers, you will be able to determine the bonuses based on the idea of breakpoints, as follows:
Total sales | Bonus |
---|---|
$0 – $9,999 | $0 |
$10,000 – $19,999 | $1,250 |
$20,000 – $29,999 | $2,500 |
$30,000 – $39,999 | $3,750 |
>= $40,000 | $5,000 |
So, let’s say you do the math manually and find that Lisa Brown made $38,000 in total sales, Betty Cole made $15,000, while Elizabeth Gardner was more successful with her total sales of $22,000. So, based on their performance, they will be receiving bonuses of $3,750, $1,250, and $2,500, respectively.
Doing calculations manually is not always fun. So, let’s see the below steps to calculate the bonus using formulas in Excel:
Step 1: In the Summary sheet, list all the sheet names you will sum. For example, in G2:G5, as shown below:
Step 2: For the first salesperson, write a SUMIF Formula for one sheet only – for example, Apr.
Step 3: Wrap the SUMIF inside SUMPRODUCT.
We need to do this because, in the next step (Step 4), we’re going to use SUMIF to sum across multiple sheets using the sheets’ reference in G2:G5. The SUMIF will return an array, so we use SUMPRODUCT to make sure that everything will get summed up correctly.
Step 4: Sum across the sheets by using the list of sheet names as a reference. Let’s use an absolute range — so, in this case,
- Replace Apr!A2:A10withINDIRECT("'"&$G$3:$G$5&"'!"&"A2:A10")
- Replace Apr!C2:C10withINDIRECT("'"&$G$3:$G$5&"'!"&"C2:C10")
The complete formula will be:
From the result in B2, you can see that Lisa Brown’s total sales for the months of April, May, and June were $38,000 in total. Her bonus will be calculated based on this amount.
Step 5: Calculate the bonus by finding the approximate matches in the lookup table. To do this, wrap the SUMPRODUCT inside VLOOKUP:
Step 6: Drag the formula down to B5 to apply the formula for other salespeople.
Congratulations! You’ve calculated the bonus for each salesperson across multiple sheets by using three functions: SUMIF, SUMPRODUCT, and VLOOKUP!