lookup and count question

bmuddy2

Active Member
Joined
Jun 22, 2004
Messages
398
hello all.

here is a question for ya.
in coulmn B i have a long list of names , many names repeat.
in column L i have a code , such as "NOT PD".

other columns are fill with other data.

my question is: in cell A1 i would like to type a name to look up.
in cell A2 i would like to type a code to look up.
formula in cell A3

for example if i type "smith" in cell A1 and "NOT PD" in cell A2.

i would like to find the number of times the name "smith" is found in column A , and the code "NOT PD" in the same row in column L.

hopefully this makes sense.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Try

=SUMPRODUCT(--(B1:B12=A1),--(L1:L12=A2))

You will have to adjust the ranges to suit. If your data is constantly changing, it may pay to create a couple of dynamic defined names to cover the data, and have the formula use those names.


HTH

Tony
 

bmuddy2

Active Member
Joined
Jun 22, 2004
Messages
398
thanks tony , works grate.

i feel my headach going away already.
 

Forum statistics

Threads
1,136,303
Messages
5,674,966
Members
419,537
Latest member
ucatchy

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