INDEX, MATCH, and COUNTIF Functions with Multiple Criteria
Vložit
- čas přidán 2. 08. 2024
- In this video, I'll guide you through multiple methods to apply INDEX, MATCH, and COUNTIF Functions with Multiple Criteria. You'll learn about combining the INDEX and MATCH functions in array and non-array formulas, and utilizing the COUNTIFS function with AND and OR logic. Applying INDEX, MATCH, and COUNTIF functions with multiple criteria can be used to analyze large datasets, create dynamic reports, extract data, perform conditional lookups, and calculate results. With practical examples and step-by-step instructions, you can effortlessly apply INDEX, MATCH, and COUNTIF functions with multiple criteria in your Excel spreadsheets.
👨🏫 Instructor: Zehad Rian Jim
🎥 Editor: Md. Riajul Islam
✨ ⯆ Resources:
Ctl+T - To insert Table
Alt + = - To apply the SUM function
▬ Contents of this video ▬▬▬▬▬▬▬▬▬▬
0:00 - Intro
0:50 - Combining INDEX and MATCH Functions in Array Formula
3:35 - Combining INDEX and MATCH Functions in Non-Array Formula
5:15 - Combining COUNTIFS, INDEX, and MATCH Functions
8:09 - Utilizing COUNTIFS Function with AND Logic
10:31 - Utilizing COUNTIFS Function with OR Logic
📚 ⯆ DOWNLOAD the workbook here:
www.exceldemy.com/index-match...
🌍 ⯆ Checkout the article here:
www.exceldemy.com/index-match...
💻 ⯆ Similar Videos:
• How to Match Multiple ...
🚩 Stay connected with us on social media for more Excel tips and tricks!
Facebook: / exceldemy
Twitter: / exceldemy
LinkedIn: / exceldemy
🙋♂️ Stuck with an Excel formula or a VBA code? You can post your questions or upload your Excel file to get in touch with the professionals and get the solution you need.
ExcelDemy Forum: exceldemy.com/forum/
👉 If you found this video helpful, don't forget to subscribe to our channel for more Excel tutorials, tips, and tricks! Hit the subscribe button and turn on notifications, so you never miss an upload. By subscribing, you'll be supporting our channel and helping us to reach more people who can benefit from our content. Thank you for watching, and we'll see you in the next video!
🔔 ⯆ Subscribe on CZcams:
/ @exceldemy2006
#excel #exceltutorial #exceltips #exceltricks
Man, you saved me, thanks for the video..!!
Hello @juniortovarquispe479,
Thanks for your appreciation it means a lot to us. Stay connected with us.
Regards
ExcelDemy
🎉super
In the combination of countifs, index, match function i want to count by the salesperson, means how many qnty are sold by the respective salesperson??
Could you pls share!!
Hello @Mnopqrstuvwxyz,
If you want to calculate the total quantity sold by any salesperson, use the following formula:
=SUMPRODUCT(($A$2:$A$10="Paul")*(B$2:B$10="x") + ($A$2:$A$10="Paul")*(C$2:C$10="x") + ($A$2:$A$10="Paul")*(D$2:D$10="x"))
You can replace the salesperson's name with a reference.
Or, if you want to count individually, you can use the following formulas:
=COUNTIFS(Table42[Salesman],"Paul",Table42[Coat],"x")
=COUNTIFS(Table42[Salesman],"Paul",Table42[Shirt],"x")
=COUNTIFS(Table42[Salesman],"Paul",Table42[T-Shirt],"x")
If you want to use drop-down list follow the steps given below:
Create Dropdown lists:
To select the salesman and product name, create two dropdown lists in cells B5 and C5. Follow this article to create a dropdown list: www.exceldemy.com/learn-excel/data-validation/drop-down-list/create/
To find the count of sales:
Use the formula in the respective cell: =SUMIFS(INDEX(B8:E16,0,MATCH(C5,B7:E7,0)),B8:B16,B5)
This formula considers duplicate names and sums up their corresponding sales for the chosen product.
To use the formula:
Select the salesman in cell B5. Then, select your desired product in cell C5. And the result will be in front of your eyes. For better understanding, download the Excel workbook.
Excel file: www.exceldemy.com/wp-content/uploads/2024/01/Counting_Based_on_Multiple_Criteria.xlsx
Regards
ExcelDemy
At 1:49, why is lookup value 1 in Match(1, ?
Hello @mysticguy7329,
In the MATCH function, 1 is used as a lookup_value because it searches for a row where all your specified conditions are true at the same time.
It checks for a match of each condition (D4=B10:B28, D5=C10:C28, D6=D10:D28). Where TRUE equals 1 and FALSE equals 0. Multiplying the conditions creates an array of 1s and 0s. Only rows that meet all conditions turn into 1. The MATCH function then looks for the first 1 in this array, meaning it finds the first row where all conditions are true. The INDEX function then returns the value from the range E10:E28 for that row.
Regards
ExcelDemy
WHICH VERSION OF MICROSOFT OFFICE THIS?
Dear, Thanks for your question! We are using Microsoft 365.
@@exceldemy2006 THANK YOU SO MUCH.....
@@DMCOMMERCIALKUMBAKONAMREGION Dear, you are very welcome!
salesmen & Products count at a time, please share the formula
Dear @user-nd5cz4mv5c,
Thank you for your question. We appreciate your feedback. Regarding your question on Salesmen and product counts at a time. Certainly, it’s possible in Excel. Just follow the steps below and check the Excel file linked to this message.
Create Dropdown lists:
To select the salesman and product name, create two dropdown lists in cells B5 and C5. Follow this article to create a dropdown list: www.exceldemy.com/learn-excel/data-validation/drop-down-list/create/
To find the count of sales:
Use the formula in the respective cell: =SUMIFS(INDEX(B8:E16,0,MATCH(C5,B7:E7,0)),B8:B16,B5)
This formula considers duplicate names and sums up their corresponding sales for the chosen product.
To use the formula:
Select the salesman in cell B5. Then, select your desired product in cell C5. And the result will be in front of your eyes. For better understanding, download the Excel workbook.
Excel file: www.exceldemy.com/wp-content/uploads/2024/01/Counting_Based_on_Multiple_Criteria.xlsx
Make sure to stay connected with ExcelDemy! 🎉❤. Have a good day.
Regards,
ExcelDemy
Suppose I have five production units & it has multiple machines that have to be serviced based on their servicing date. Now I want to count how many machines are serviced & balanced based on their units??
Hello @Mnopqrstuvwxyz,
Assuming you have a dataset like this:
Unit Machine Status Servicing Date
Unit 1 Machine A Serviced 01/01/2024
Unit 2 Machine B Balanced 05/01/2024
Unit 1 Machine C Serviced 10/01/2024
Unit 2 Machine D Serviced 15/01/2024
Unit 1 Machine E Balanced 20/01/2024
To count serviced per unit, use the following formula:
=COUNTIFS(A:A, "Unit 1", C:C, "Serviced")
To count balanced per unit, use a similar formula:
=COUNTIFS(A:A, "Unit 1", C:C, "Balanced")
Please adjust the formula based on your dataset.
Regards
ExcelDemy
the first formula is not working for me... I don't understand why
Hello @ratulmitra347 ,
Please share your sample dataset in ExcelDemy Forum along with the error the formula is returning.
exceldemy.com/forum/
We will lookup your formula to find the reason of not working.
Also, you can download our Excel file and try to replace it with your dataset.
www.exceldemy.com/index-match-countif-multiple-criteria/#download
www.exceldemy.com/wp-content/uploads/2021/06/Multiple-Criteria-in-Excel.xlsx
Regards
ExcelDemy