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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,650
Messages
5,660,120
Members
418,550
Latest member
kp_

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