Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 " wrote: 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 |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions |