Formula needed to count repeated data based on criterias

[Cheslop84]

Hi

I need a formula that will count how many styles I am carrying per brand. The table array where I hold my data is shown below, cells A-D.

Cell B states the name on the style, cell A is the size breakdown for that particular style.
Cell C shows the brand attached to that style

Cell D is stock holding for the individual sizes of that style.

What I am looking to do is count how many styles I have in stock based on the brand (which will alternate as I am using a list function from validation which I can then scroll through to display data for the brand of my choosing) and also the style has to have a qty of > 0. That's a qty of >0 for the whole style, not just the individual sizes.

For example, from below, if K33076Dress32849Rasb/Cream was qty 0 for each size then I wouldnt want it to count the style, but if just one size has a qty, I would like it would count it.

Hope someone can help

Thanks

[TABLE="width: 593"]
<tbody>[TR]
[TD="class: et3, width: 288"]KA3076 Dress 32849 Rasberry/Cream S[/TD]
[TD="class: et3, width: 64"]KA3076Dress32849Rasb/Cream[/TD]
[TD="class: et3, width: 190"]Glamorous Old[/TD]
[TD="class: et3, width: 51"]2[/TD]
[/TR]
[TR]
[TD="class: et4, width: 288"]KA3076 Dress 32849 Rasberry/Cream M[/TD]
[TD="class: et4, width: 64"]KA3076Dress32849Rasb/Cream[/TD]
[TD="class: et4, width: 190"]Glamorous Old[/TD]
[TD="class: et4, width: 51"]-1[/TD]
[/TR]
[TR]
[TD="class: et3, width: 288"]KA3076 Dress 32849 Rasberry/Cream L[/TD]
[TD="class: et3, width: 64"]KA3076Dress32849Rasb/Cream[/TD]
[TD="class: et3, width: 190"]Glamorous Old[/TD]
[TD="class: et3, width: 51"]0[/TD]
[/TR]
[TR]
[TD="class: et4, width: 288"]CK0550 Dress 32029 Rust XS[/TD]
[TD="class: et4, width: 64"]CK0550Dress32029Rust[/TD]
[TD="class: et4, width: 190"]Glamorous Old[/TD]
[TD="class: et4, width: 51"]0[/TD]
[/TR]
[TR]
[TD="class: et3, width: 288"]CK0550 Dress 32029 Rust S[/TD]
[TD="class: et3, width: 64"]CK0550Dress32029Rust[/TD]
[TD="class: et3, width: 190"]Glamorous Old[/TD]
[TD="class: et3, width: 51"]0[/TD]
[/TR]
[TR]
[TD="class: et4, width: 288"]CK0550 Dress 32029 Rust M[/TD]
[TD="class: et4, width: 64"]CK0550Dress32029Rust[/TD]
[TD="class: et4, width: 190"]Glamorous Old[/TD]
[TD="class: et4, width: 51"]0[/TD]
[/TR]
[TR]
[TD="class: et3, width: 288"]CK0550 Dress 32029 Rust L[/TD]
[TD="class: et3, width: 64"]CK0550Dress32029Rust[/TD]
[TD="class: et3, width: 190"]Glamorous Old[/TD]
[TD="class: et3, width: 51"]0[/TD]
[/TR]
[TR]
[TD="class: et4, width: 288"]1393 Ladies dress-32322 Black/mustard XS[/TD]
[TD="class: et4, width: 64"]AN1393dress-32322Blk/mustard[/TD]
[TD="class: et4, width: 190"]Glamorous Old[/TD]
[TD="class: et4, width: 51"]0[/TD]
[/TR]
[TR]
[TD="class: et3, width: 288"]N1393 Ladies dress-32322 Black/mustard S[/TD]
[TD="class: et3, width: 64"]AN1393dress-32322Blk/mustard[/TD]
[TD="class: et3, width: 190"]Glamorous Old[/TD]
[TD="class: et3, width: 51"]1[/TD]
[/TR]
[TR]
[TD="class: et4, width: 288"]N1393 Ladies dress-32322 Black/mustard M[/TD]
[TD="class: et4, width: 64"]AN1393dress-32322Blk/mustard[/TD]
[TD="class: et4, width: 190"]Glamorous Old[/TD]
[TD="class: et4, width: 51"]0[/TD]
[/TR]
[TR]
[TD="class: et3, width: 288"]N1393 Ladies dress-32322 Black/mustard L[/TD]
[TD="class: et3, width: 64"]AN1393dress-32322Blk/mustard[/TD]
[TD="class: et3, width: 190"]Glamorous Old[/TD]
[TD="class: et3, width: 51"]1[/TD]
[/TR]
[TR]
[TD="class: et4, width: 288"]Fur Collar Coat Green S[/TD]
[TD="class: et4, width: 64"]FurCollarCoatGreen[/TD]
[TD="class: et4, width: 190"]Fast Fashion Womens Old[/TD]
[TD="class: et4, width: 51"]0[/TD]
[/TR]
[TR]
[TD="class: et3, width: 288"]Fur Collar Coat Green M[/TD]
[TD="class: et3, width: 64"]FurCollarCoatGreen[/TD]
[TD="class: et3, width: 190"]Fast Fashion Womens Old[/TD]
[TD="class: et3, width: 51"]-1[/TD]
[/TR]
[TR]
[TD="class: et4, width: 288"]Fur Collar Coat Green L[/TD]
[TD="class: et4, width: 64"]FurCollarCoatGreen[/TD]
[TD="class: et4, width: 190"]Fast Fashion Womens Old[/TD]
[TD="class: et4, width: 51"]1[/TD]
[/TR]
[TR]
[TD="class: et3, width: 288"]Fur Collar Coat Green XL[/TD]
[TD="class: et3, width: 64"]FurCollarCoatGreen[/TD]
[TD="class: et3, width: 190"]Fast Fashion Womens Old[/TD]
[TD="class: et3, width: 51"]0[/TD]
[/TR]
[TR]
[TD="class: et4, width: 288"]Fur Collar Coat Green XS[/TD]
[TD="class: et4, width: 64"]FurCollarCoatGreen[/TD]
[TD="class: et4, width: 190"]Fast Fashion Womens Old[/TD]
[TD="class: et4, width: 51"]0[/TD]
[/TR]
[TR]
[TD="class: et3, width: 288"]NL2785 Ladies Parka Khaki 8[/TD]
[TD="class: et3, width: 64"]NL2785ParkaKhaki[/TD]
[TD="class: et3, width: 190"]Fast Fashion Womens Old[/TD]
[TD="class: et3, width: 51"]0[/TD]
[/TR]
[TR]
[TD="class: et4, width: 288"]NL2785 Ladies Parka Khaki 10[/TD]
[TD="class: et4, width: 64"]NL2785ParkaKhaki[/TD]
[TD="class: et4, width: 190"]Fast Fashion Womens Old[/TD]
[TD="class: et4, width: 51"]0[/TD]
[/TR]
[TR]
[TD="class: et3, width: 288"]NL2785 Ladies Parka Khaki 12[/TD]
[TD="class: et3, width: 64"]NL2785ParkaKhaki[/TD]
[TD="class: et3, width: 190"]Fast Fashion Womens Old[/TD]
[TD="class: et3, width: 51"]0[/TD]
[/TR]
[TR]
[TD="class: et4, width: 288"]NL2785 Ladies Parka Khaki 14[/TD]
[TD="class: et4, width: 64"]NL2785ParkaKhaki[/TD]
[TD="class: et4, width: 190"]Fast Fashion Womens Old[/TD]
[TD="class: et4, width: 51"]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[J.Ty.]

Hi,

Please test the following solution:

Excel 2016 (Windows) 64 bit
	A	B	C	D	E	F
1	KA3076 Dress 32849 Rasberry/Cream S	KA3076Dress32849Rasb/Cream	Glamorous Old	2	brand	# of styles
2	KA3076 Dress 32849 Rasberry/Cream M	KA3076Dress32849Rasb/Cream	Glamorous Old	-1	Fast Fashion Womens Old	2
3	KA3076 Dress 32849 Rasberry/Cream L	KA3076Dress32849Rasb/Cream	Glamorous Old	0
4	CK0550 Dress 32029 Rust XS	CK0550Dress32029Rust	Glamorous Old	0
5	CK0550 Dress 32029 Rust S	CK0550Dress32029Rust	Glamorous Old	0
6	CK0550 Dress 32029 Rust M	CK0550Dress32029Rust	Glamorous Old	0
7	CK0550 Dress 32029 Rust L	CK0550Dress32029Rust	Glamorous Old	0
8	1393 Ladies dress-32322 Black/mustard XS	AN1393dress-32322Blk/mustard	Glamorous Old	0
9	N1393 Ladies dress-32322 Black/mustard S	AN1393dress-32322Blk/mustard	Glamorous Old	1
10	N1393 Ladies dress-32322 Black/mustard M	AN1393dress-32322Blk/mustard	Glamorous Old	0
11	N1393 Ladies dress-32322 Black/mustard L	AN1393dress-32322Blk/mustard	Glamorous Old	1
12	Fur Collar Coat Green S	FurCollarCoatGreen	Fast Fashion Womens Old	0
13	Fur Collar Coat Green M	FurCollarCoatGreen	Fast Fashion Womens Old	-1
14	Fur Collar Coat Green L	FurCollarCoatGreen	Fast Fashion Womens Old	2
15	Fur Collar Coat Green XL	FurCollarCoatGreen	Fast Fashion Womens Old	0
16	Fur Collar Coat Green XS	FurCollarCoatGreen	Fast Fashion Womens Old	0
17	NL2785 Ladies Parka Khaki 8	NL2785ParkaKhaki	Fast Fashion Womens Old	0
18	NL2785 Ladies Parka Khaki 10	NL2785ParkaKhaki	Fast Fashion Womens Old	0
19	NL2785 Ladies Parka Khaki 12	NL2785ParkaKhaki	Fast Fashion Womens Old	0
20	NL2785 Ladies Parka Khaki 14	NL2785ParkaKhaki	Fast Fashion Womens Old	2
Sheet3

Cell Formulas
Range	Formula
F2	{=SUM(($D$1:$D$20>0)*IFERROR(1/COUNTIFS($C$1:$C$20,E2,$D$1:$D$20,">0",$B$1:$B$20,$B$1:$B$20),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.