Lookup a value and count the number of associated occurences
 

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

Lookup a value and count the number of associated occurences
 

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)

"reddy" wrote in message
...
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

Lookup a value and count the number of associated occurences
 

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


"reddy" wrote:

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

Lookup a value and count the number of associated occurences
 

Thank you both! That worked!

"Bob Phillips" wrote:

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)

"reddy" wrote in message
...
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