Lookup a value and count the number of associated occurences

  • Thread starter Thread starter reddy
  • Start date Start date
R

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
 
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)
 
Thank you both! That worked!

Bob Phillips said:
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)
Click to expand...
 
Back
Top