Help with VLOOKUP and SUMPRODUCT formula

karlton69710

New Member
Joined
May 14, 2014
Messages
2
Hi all,

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

ABCDE
1Name
Start Date
Team
Language
Agent
2Bob Smith01/01/2014GarethEnglishNew
3Jenny Lemon01/01/2014BenEnglishExperienced
4Richard Smith01/01/2014LauraEnglishExperienced
5Steve Yellow01/01/2014BenEnglishExperienced
6Carol Winters01/01/2014BenEnglishNew
7Jean Sugars01/01/2014LauraEnglishExperienced
8Pauline King01/01/2014BenEnglishNew
9Graham Jewell01/01/2014GarethEnglishNew
10John Doe01/01/2014GarethEnglishNew
11
12New
13Experienced

<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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
maybe something like...

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

in D12 and dragged down to D13
 

Forum statistics

Threads
1,136,969
Messages
5,678,893
Members
419,787
Latest member
juanam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top