Formula for COUNTIF with more than one VLOOKUP condition / criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-1466762%22%20slang%3D%22en-US%22%3EFormula%20for%20COUNTIF%20with%20more%20than%20one%20VLOOKUP%20condition%20%2F%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1466762%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHi%2C%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EI'm%20trying%20to%20generate%20a%20formula%20that%20will%20count%20the%20number%20of%20experts%20with%20specific%20expertise%20in%20each%20country%20and%20generate%20the%20result%20in%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EColumn%20K%20(red%20text).%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3ETable%201%20shows%20experts%20already%20available%20in%20each%20country%2C%20and%20their%20areas%20of%20expertise.%20Table%202%20shows%20expertise%20that%20each%20country%20is%20requesting%20for.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSTRONG%3EThe%20aim%20is%20to%20assist%20decision%20making%20by%20reviewing%20whether%20the%20experts%20requested%20for%20(Table%202)%20for%20each%20country%20already%20exist%20in%20the%20same%20country%20(Table%201)%2C%20and%20if%20yes%2C%20how%20many%3F%20If%20no%2C%20also%20important%20to%20know.%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20tried%20but%20got%20stuck%20with%20combining%20COUNTIF%20with%20two%20VLOOKUP%20conditions.%20My%20challenge%20is%20to%20have%20two%20conditions%20that%20must%20be%20fulfilled%20in%20the%20formula%3A%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E(a)%20COUNT%20IF%20the%20Country%20(Column%20H)%20in%20Table%202%20is%20an%20exact%20match%20for%20the%20Country%20(Column%20A)%20in%20Table%201.%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EAND%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E(b)%20COUNT%20IF%20the%20%22Expertise%22%20(Column%20C)%20is%20an%20exact%20match%20for%20%22Expertise%20Requested%20For%22%20(Column%20J)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EThese%20are%20two%20huge%20tables%20in%20actuality%2C%20and%20will%20refer%20to%20the%20actual%20table%20references%20in%20the%20formula%20e.g.%20Experts_Table%5BExpertise%5D%20and%20Requests_Table%5BExpertise%20Requested%20For%5D%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EThank%20you%20for%20the%20support!%3C%2FSPAN%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%3ERegards%2C%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EMark%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1466762%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1466918%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20COUNTIF%20with%20more%20than%20one%20VLOOKUP%20condition%20%2F%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1466918%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F700388%22%20target%3D%22_blank%22%3E%40Bonyo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCOUNTIFS(Experts_Table%5BCountry%5D%2C%5B%40Country%5D%2CExperts_Table%5BExpertise%5D%2C%5B%40%5BExpertise%20Requested%20For%5D%5D)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1467158%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20COUNTIF%20with%20more%20than%20one%20VLOOKUP%20condition%20%2F%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1467158%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F700388%22%20target%3D%22_blank%22%3E%40Bonyo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20you%20were%20nearly%20there%20-%20but%20use%20COUNTIFS%20instead%20of%20COUNTIF%20-%20see%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20is%20what%20you%20need!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPeter%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hi,

 
I'm trying to generate a formula that will count the number of experts with specific expertise in each country and generate the result in Column K (red text).

Table 1 shows experts already available in each country, and their areas of expertise. Table 2 shows expertise that each country is requesting for.

The aim is to assist decision making by reviewing whether the experts requested for (Table 2) for each country already exist in the same country (Table 1), and if yes, how many? If no, also important to know.

I tried but got stuck with combining COUNTIF with two VLOOKUP conditions. My challenge is to have two conditions that must be fulfilled in the formula:
(a) COUNT IF the Country (Column H) in Table 2 is an exact match for the Country (Column A) in Table 1. 
AND
(b) COUNT IF the "Expertise" (Column C) is an exact match for "Expertise Requested For" (Column J)


These are two huge tables in actuality, and will refer to the actual table references in the formula e.g. Experts_Table[Expertise] and Requests_Table[Expertise Requested For]

Thank you for the support!
 
Regards,
 
Mark
2 Replies
Highlighted

@Bonyo 

That could be

=COUNTIFS(Experts_Table[Country],[@Country],Experts_Table[Expertise],[@[Expertise Requested For]])
Highlighted

@Bonyo 

Hi 

 

I think you were nearly there - but use COUNTIFS instead of COUNTIF - see attached.

 

Hope this is what you need!

 

Peter