Help with VLOOKUP and SUMPRODUCT formula

[karlton69710]

Hi all,

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

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Name
[/TD]
[TD="align: center"]Start Date
[/TD]
[TD="align: center"]Team
[/TD]
[TD="align: center"]Language
[/TD]
[TD="align: center"]Agent
[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Bob Smith[/TD]
[TD="align: center"]01/01/2014[/TD]
[TD="align: center"]Gareth[/TD]
[TD="align: center"]English[/TD]
[TD="align: center"]New[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Jenny Lemon[/TD]
[TD="align: center"]01/01/2014[/TD]
[TD="align: center"]Ben[/TD]
[TD="align: center"]English[/TD]
[TD="align: center"]Experienced[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Richard Smith[/TD]
[TD="align: center"]01/01/2014[/TD]
[TD="align: center"]Laura[/TD]
[TD="align: center"]English[/TD]
[TD="align: center"]Experienced[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Steve Yellow[/TD]
[TD="align: center"]01/01/2014[/TD]
[TD="align: center"]Ben[/TD]
[TD="align: center"]English[/TD]
[TD="align: center"]Experienced[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Carol Winters[/TD]
[TD="align: center"]01/01/2014[/TD]
[TD="align: center"]Ben[/TD]
[TD="align: center"]English[/TD]
[TD="align: center"]New[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Jean Sugars[/TD]
[TD="align: center"]01/01/2014[/TD]
[TD="align: center"]Laura[/TD]
[TD="align: center"]English[/TD]
[TD="align: center"]Experienced[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]Pauline King[/TD]
[TD="align: center"]01/01/2014[/TD]
[TD="align: center"]Ben[/TD]
[TD="align: center"]English[/TD]
[TD="align: center"]New[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]Graham Jewell[/TD]
[TD="align: center"]01/01/2014[/TD]
[TD="align: center"]Gareth[/TD]
[TD="align: center"]English[/TD]
[TD="align: center"]New[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]John Doe[/TD]
[TD="align: center"]01/01/2014[/TD]
[TD="align: center"]Gareth[/TD]
[TD="align: center"]English[/TD]
[TD="align: center"]New[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]New
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Experienced
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

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