Counting with multiple criteria

[ChuckDrago]

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

 

[C Moore]

Sounds like there should be a column of the different codes and next to it a count if statement. Unless I'm missing something.

Col E Col F
A =countif(Col B, [cell to left])
B =countif(Col B, [cell to left])
C =countif(Col B, [cell to left])
D =countif(Col B, [cell to left])
.
.
.

 

[AhoyNC]

Maybe something like this. Copy formula down and across as needed.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Prod. ID	Return Code		ID / Return	A	B	C	D	E	F
2	ID100	D		ID100	1	0	0	2	0	1
3	ID106	F		ID106	0	0	1	0	1	2
4	ID102	A		ID102	1	0	0	0	0	0
5	ID100	A		ID104	0	1	0	0	0	0
6	ID104	B		ID105	0	0	1	0	0	0
7	ID105	C		ID107	0	0	1	0	0	0
8	ID106	C		ID112	1	0	1	0	0	0
9	ID107	C		ID111	1	0	0	0	0	0
10	ID100	D		ID114	0	0	0	0	1	0
11	ID106	E		ID116	0	0	0	0	1	0
12	ID112	C		ID118	0	0	2	0	0	0
13	ID111	A		ID119	0	1	0	0	0	0
14	ID112	A
15	ID106	F
16	ID114	E
17	ID100	F
18	ID116	E
19	ID118	C
20	ID118	C
21	ID119	B
Sheet

 

[ChuckDrago]

Maybe something like this. Copy formula down and across as needed.

AhoyNC... That is sweeeeet!. Rather than looking for a formula to calculate each condition, multi-copying your suggested formula yields a kinda of a live set! Actually I can extend the copying to additional rows, so that when new merchandise is added it is already set for returns computation. Obviously we would love no returns but...
Thanks again!
Chuck

 

[BlueHornet]

One thing to keep in mind when using SUMPRODUCT is that both ranges have to be at least roughly congruent. That is, if the first range is A1:A100, then the second range also has to have 100 rows. It cannot have more or fewer rows.

 

[ChuckDrago]

One thing to keep in mind when using SUMPRODUCT is that both ranges have to be at least roughly congruent. That is, if the first range is A1:A100, then the second range also has to have 100 rows. It cannot have more or fewer rows.

Thanks BH... I adopted AhoyNC's solution but your point is well taken, in case of a future use of SUMPRODUCT. I have used that powerful function before but never with multiple criteria. Your observation comes handy for the latter.
Chuck

 

[AhoyNC]

You're welcome. Thanks for the feedback.
You could use SUMPRODUCT in the example above. So formula in E2 would be:

[TABLE="width: 382"]
<colgroup><col width="382"></colgroup><tbody>[TR]
   [TD="width: 382"]SUMPRODUCT(($A$2:$A$21=$D2)*($B$2:$B$21=E$1))[/TD]
 [/TR]
</tbody>[/TABLE]

Copy down and across.