Multiconditional, dictionary-based, count in Excel 2019-CodePudding

Excel coding gurus, can someone help me count in Excel? :) I need a count, based on multiple, dictionaty depandant, conditions.

What I have: I have an Excel 2019. Not the 365 edition. I have an Excel sheet called, say Dicts with a table of 2 columns. I is a dictionary. Column I contains list of people. And each person in the I column has the country of his/her origin denoted in the correspondent cell of H column.

For a better view check this picture, plz.

And I have a DataSheet, that contains records of various persons from the dictionary table along wiht some data on each record. For a better view check this picture, plz.

Now, the question is: How can I count the number of all the citizens of USA and Iitaly in the column A that have either Y or M in the correspondent cell of Column B?

CodePudding user response：

You could use the following in Office 365:

=LET(condition1,FILTER(A2:A25,MMULT(--({"Y","M"}=B2:B25),SEQUENCE(2,,1,0))),
     condition2,FILTER(Table1[C2],MMULT(--({"USA","Irtaly"}=Table1[C1]),SEQUENCE(2,,1,0))),
SUM(--(TRANSPOSE(condition1)=condition2)))

(Change the table references to your needs)

CodePudding user response：

I can imagine you're looking for something simpler, but what about this (all based on COUNTIFS() worksheet function):

=COUNT_Multiple_Criteria(range,country="USA", done="Y")   
 COUNT_Multiple_Criteria(range,country="USA", done="M")   
 COUNT_Multiple_Criteria(range,country="Italy", done="Y")  
 COUNT_Multiple_Criteria(range,country="Italy", done="M")