Count

[hansjhamm]

I have tried to find the solution to this, to no avail...and need some
insight.
In the workbook; 1 sheet is "store data" and the other is "compiled
data".
The store data sheet contains information about stores, while the
compiled
brings forward the data by district manager etc...
Here is what I need to accomplish:
1 - In the compiled sheet I have DM's name
2 - I need to find where that DM is located in the store data sheet
3 - Then count in an adjacent column how many times a certain code
(number) occurs
4 - Then return that qty. back in a specific cell in the compiled sheet

Example:
Compiled Sheet
Column A, Column c
Joe, (times code appears)

Store Data sheet
Column A, Column D
Joe, 1
Joe, 1
Joe, 2
Joe, 3


So in the compiled sheet I would have
Column A, Column B, Column C
row 1 Joe 1 (the code #) 2(times the code appears)
row 2 Joe 2 (the code #) 1(times the code appears)
row 3 Joe 3 (the code #) 1(times the code appears)

I have searched trying to find the answer, and hope that some of you
Excel GURU's can help...

Thank you all for help!!

Hans

 

[Guest]

I'm not an excel Guru but try something like this:

=SUMPRODUCT(('Store data'!$A$1:$A$1000=A1)*('Store data'!$C$1:$C$1000=B1))
where A1 is a DM's name and B1 is a code.

Note...you can not use full column reference in a sumproduct formula

HTH
JG

 

[hansjhamm]

Pinmaster...thanks alot! worked like a charm. Did not know that you
could not use a full column refernce for sumproduct. That is why I
could not get it to work. Once again THANKS!!

Has