lookup and count question

[bmuddy2]

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.

 

[acw]

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]

thanks tony , works grate.

i feel my headach going away already.