ChuckDrago
Active Member
- Joined
- Sep 7, 2007
- Messages
- 470
- Office Version
- 2010
- Platform
- Windows
Hi everyone,
I have been asked to provide charts for returned merchandise by return code. I initially imagined building a table where the merchandise ID was in column "A" and column "B" will have the return codes, which are A,B,C,D,E and F. Therefore to graph the results I would need to count the occurrences by code for each merchandise ID, such as ID 12345 has 3 A's, 5 B's, no C's...etc.
I encountered a formula that appeared to do the trick but I can't make it to work. Therefore the idea is two fold:
a) Request help in making it work, or
b) If there is a simpler approach get guidelines to it.
The formula in question is
<code/> '=SUMPRODUCT((rng1 = criteria1)*(rng2=criteria2)*(SUBTOTAL(3,OFFSET(rng,rows,0,1))))</code>
I interpreted range1 to be "A2:A100", criteria1 = xxxxx (variable - each Merchandise ID), rng2 as "B2:B200" and criteria2 as ("x",variable - each return code), to define the places to count the number of "x"s tied to Item xxxxx. However, I fail to understand the rng and rows in the Offset section.
Tried several possibilities but some returned a 0 and others VALUE Error.
I will appreciate your help,
Chuck
I have been asked to provide charts for returned merchandise by return code. I initially imagined building a table where the merchandise ID was in column "A" and column "B" will have the return codes, which are A,B,C,D,E and F. Therefore to graph the results I would need to count the occurrences by code for each merchandise ID, such as ID 12345 has 3 A's, 5 B's, no C's...etc.
I encountered a formula that appeared to do the trick but I can't make it to work. Therefore the idea is two fold:
a) Request help in making it work, or
b) If there is a simpler approach get guidelines to it.
The formula in question is
<code/> '=SUMPRODUCT((rng1 = criteria1)*(rng2=criteria2)*(SUBTOTAL(3,OFFSET(rng,rows,0,1))))</code>
I interpreted range1 to be "A2:A100", criteria1 = xxxxx (variable - each Merchandise ID), rng2 as "B2:B200" and criteria2 as ("x",variable - each return code), to define the places to count the number of "x"s tied to Item xxxxx. However, I fail to understand the rng and rows in the Offset section.
Tried several possibilities but some returned a 0 and others VALUE Error.
I will appreciate your help,
Chuck