ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Lookup a value and count the number of associated occurences (https://www.excelbanter.com/excel-discussion-misc-queries/200503-lookup-value-count-number-associated-occurences.html)

reddy

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

Bob Phillips

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




Teethless mama

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


reddy

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






All times are GMT +1. The time now is 03:46 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com