Lookup a value and count the number of associated occurences

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

date: Wed, 27 Aug 2008 15:10:12 -0700 author: reddy

RE: 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

date: Wed, 27 Aug 2008 15:27:11 -0700 author: Teethless mama

Re: 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 
news:500F3B25-5C68-468C-A1E0-4FCB0BE6B485@microsoft.com...
>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

date: Wed, 27 Aug 2008 23:26:51 +0100 author: Bob Phillips

Re: 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 
> news:500F3B25-5C68-468C-A1E0-4FCB0BE6B485@microsoft.com...
> >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 
> 
> 
>

date: Wed, 27 Aug 2008 15:37:02 -0700 author: reddy