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[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Sarah Green
[/TD]
[TD]Marketing
[/TD]
[TD]CV Review - HR
[/TD]
[/TR]
[TR]
[TD]Mark Jones
[/TD]
[TD]Marketing
[/TD]
[TD]CV Review - Business
[/TD]
[/TR]
[TR]
[TD]Matthew Phillips
[/TD]
[TD]Sales
[/TD]
[TD]1st Interview
[/TD]
[/TR]
[TR]
[TD]Lisa Davies
[/TD]
[TD]Finance
[/TD]
[TD]CV Review - Business
[/TD]
[/TR]
</tbody>[/TABLE]


Dashboard[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Roles (A1)
[/TD]
[TD]CV Screening (B1)
[/TD]
[TD]1st Interview (C1)
[/TD]
[/TR]
[TR]
[TD]Marketing (A2)
[/TD]
[TD]B2
[/TD]
[TD]C2
[/TD]
[/TR]
[TR]
[TD]Sales (A3)
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Finance (A4)
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Hi and welcome to Mr Excel forum

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

Dashboard

[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Roles​

[/TD]
[TD]

CV Screening​

[/TD]
[TD]

1st Interview​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

Marketing​

[/TD]
[TD]

2​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

Sales​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

Finance​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[/TR]
</TBODY>[/TABLE]


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.