INDEX, MATCH, and COUNTIF Functions with Multiple Criteria

[@juniortovarquispe479]

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

[@exceldemy2006]

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

[@shyjumathew07]

Excellent

[@exceldemy2006]

Hello @shyjumathew07, Thanks for your feedback. We appreciate your feedback! Keep exploring the power of INDEX, MATCH, and COUNTIF with ExcelDemy! Regards ExcelDemy

[@channasandramjallikattukin478]

🎉super

[@mysticguy7329]

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

[@exceldemy2006]

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

[@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

[@AbubakarSiddik-v3l]

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

[@exceldemy2006]

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

[@DMCOMMERCIALKUMBAKONAMREGION]

WHICH VERSION OF MICROSOFT OFFICE THIS?

[@exceldemy2006]

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

[@DMCOMMERCIALKUMBAKONAMREGION]

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

[@exceldemy2006]

@@DMCOMMERCIALKUMBAKONAMREGION Dear, you are very welcome!

[@ratulmitra347]

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

[@exceldemy2006]

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