Lookup a value and count the number of associated occurences

[reddy]

I have 2 worksheets. I want to obtain the summary counts in worksheet 1 from
Worksheet 2. What formula should I use to poulate worksheet 1.

Worksheet 1
2008 File counts by Lender and Month
Lender Jan Feb Mar
Aurora 3 2 4
BofA 6 7 8
CCM 8 9 5

Worksheet 2
Jan Aurora 1
Jan Aurora 1
Jan Aurora 1
Feb Aurora 1
Feb Aurora 1
Mar Aurora 1
Mar Aurora 1
Mar Aurora 1
Mar Aurora 1

 

[Bob Phillips]

B2: =SUMPRODUCT(--(Sheet2!$A$2:$A$200=B$1),--(Sheet2!$B$2:$B$200=A$2))

copy down and across

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Teethless mama]

=SUMPRODUCT(--(Sheet2!$A$1:$A$9=B$1),--(Sheet2!$B$1:$B$9=$A2))

 

[reddy]

Thank you both! That worked!

B2: =SUMPRODUCT(--(Sheet2!$A$2:$A$200=B$1),--(Sheet2!$B$2:$B$200=A$2))

copy down and across

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)