INDEX, MATCH, and COUNTIF Functions with Multiple Criteria

Подписаться 11 тыс.

Просмотров 14 тыс.

50% 1

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 RU-vid:
/ @exceldemy2006
#excel #exceltutorial #exceltips #exceltricks

Опубликовано:

30 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 17

@juniortovarquispe479 4 месяца назад

Man, you saved me, thanks for the video..!!

@exceldemy2006 4 месяца назад

Hello @juniortovarquispe479, Thanks for your appreciation it means a lot to us. Stay connected with us. Regards ExcelDemy

@Mnopqrstuvwxyz.... Месяц назад

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!!

@exceldemy2006 Месяц назад

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

@channasandramjallikattukin478 Год назад

🎉super

@mysticguy7329 4 месяца назад

At 1:49, why is lookup value 1 in Match(1, ?

@exceldemy2006 4 месяца назад

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

@ratulmitra347 2 месяца назад

the first formula is not working for me... I don't understand why

@exceldemy2006 2 месяца назад

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

@DMCOMMERCIALKUMBAKONAMREGION 2 месяца назад

WHICH VERSION OF MICROSOFT OFFICE THIS?

@exceldemy2006 2 месяца назад

Dear, Thanks for your question! We are using Microsoft 365.

@DMCOMMERCIALKUMBAKONAMREGION 2 месяца назад

@@exceldemy2006 THANK YOU SO MUCH.....

@exceldemy2006 2 месяца назад

@@DMCOMMERCIALKUMBAKONAMREGION Dear, you are very welcome!

@user-nd5cz4mv5c 7 месяцев назад

salesmen & Products count at a time, please share the formula

@exceldemy2006 7 месяцев назад

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

@Mnopqrstuvwxyz.... Месяц назад

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??

@exceldemy2006 Месяц назад

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