How do I count the number of times a text value appears but only for certain criteria?

[Baywaah]

My apologies if there is something already out there on this but I can't find anything that answers my issue.

I have 2 worksheets. One is a table of data and the other is (will be if i can get this to work!) a sumamry of dashboard of that data. Here's what I want to do:

In my dashboard table, I want to display the number of times that a text value occurs for certain roles to show how many people are at various stages of the recruitment process . Some example data below. So, on the dashboard in B2 the result should say 2. I want it to look up the value in column a, check the data sheet to see where that value exists and then count values "CV Review - HR" and "CV Review - Business".

Any help VERY MUCH appreciated.

Data in sheet 1

Sarah Green	Marketing	CV Review - HR
Mark Jones	Marketing	CV Review - Business
Matthew Phillips	Sales	1st Interview
Lisa Davies	Finance	CV Review - Business

<tbody>
</tbody>

Dashboard

Roles (A1)	CV Screening (B1)	1st Interview (C1)
Marketing (A2)	B2	C2
Sales (A3)
Finance (A4)

<tbody>
</tbody>

 

[Marcelo Branco]

Hi and welcome to Mr Excel forum

Assuming data in columns A:C of Sheet1, maybe something like this

Dashboard

	A​	B​	C​
1​	Roles​	CV Screening​	1st Interview​
2​	Marketing​	2​	0​
3​	Sales​	0​	1​
4​	Finance​	1​	0​

<TBODY>
</TBODY>

Formula in B2 copied down
=SUMPRODUCT(--(Sheet1!$B$1:$B$100=A2),--ISNUMBER(MATCH(Sheet1!$C$1:$C$100,{"CV Review - HR";"CV Review - Business"},0)))

Formula in C2 copied down
=SUMPRODUCT(--(Sheet1!$B$1:$B$100=A2),--(Sheet1!$C$1:$C$100="1st Interview"))

Hope this helps

M.