Help with VLOOKUP and SUMPRODUCT formula

[karlton69710]

Hi all,

I am wondering if you could help me with the following?

	A	B	C	D	E
1	Name	Start Date	Team	Language	Agent
2	Bob Smith	01/01/2014	Gareth	English	New
3	Jenny Lemon	01/01/2014	Ben	English	Experienced
4	Richard Smith	01/01/2014	Laura	English	Experienced
5	Steve Yellow	01/01/2014	Ben	English	Experienced
6	Carol Winters	01/01/2014	Ben	English	New
7	Jean Sugars	01/01/2014	Laura	English	Experienced
8	Pauline King	01/01/2014	Ben	English	New
9	Graham Jewell	01/01/2014	Gareth	English	New
10	John Doe	01/01/2014	Gareth	English	New
11
12			New
13			Experienced

<tbody>
</tbody>

Above you will see some mock data for a table similar to one I work with. What I am trying to do is come up with a formula for cell D12 that will search for the name Gareth in column C (Range C2:C10) and then search for the word New in column E (range C2:C10)

The answer in D12 would be 3.

I would then want to adapt this formula for D13 to search for Gareth and Experienced to which it would come up with 0.

Can anyone help with this?

Many thanks,

Karl

 

[Weazel]

maybe something like...

=COUNTIFS($C$2:$C$10,"Gareth",$E$2:$E$10,C12)

in D12 and dragged down to D13

 

[karlton69710]

Wow that was so simple! Seems I was overcomplicating the formula! It worked, thank you