Formula needed to count repeated data based on criterias

Cheslop84

New Member
Joined
Aug 24, 2016
Messages
5
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

KA3076 Dress 32849 Rasberry/Cream SKA3076Dress32849Rasb/CreamGlamorous Old2
KA3076 Dress 32849 Rasberry/Cream MKA3076Dress32849Rasb/CreamGlamorous Old-1
KA3076 Dress 32849 Rasberry/Cream LKA3076Dress32849Rasb/CreamGlamorous Old0
CK0550 Dress 32029 Rust XSCK0550Dress32029RustGlamorous Old0
CK0550 Dress 32029 Rust SCK0550Dress32029RustGlamorous Old0
CK0550 Dress 32029 Rust MCK0550Dress32029RustGlamorous Old0
CK0550 Dress 32029 Rust LCK0550Dress32029RustGlamorous Old0
1393 Ladies dress-32322 Black/mustard XSAN1393dress-32322Blk/mustardGlamorous Old0
N1393 Ladies dress-32322 Black/mustard SAN1393dress-32322Blk/mustardGlamorous Old1
N1393 Ladies dress-32322 Black/mustard MAN1393dress-32322Blk/mustardGlamorous Old0
N1393 Ladies dress-32322 Black/mustard LAN1393dress-32322Blk/mustardGlamorous Old1
Fur Collar Coat Green SFurCollarCoatGreenFast Fashion Womens Old0
Fur Collar Coat Green MFurCollarCoatGreenFast Fashion Womens Old-1
Fur Collar Coat Green LFurCollarCoatGreenFast Fashion Womens Old1
Fur Collar Coat Green XLFurCollarCoatGreenFast Fashion Womens Old0
Fur Collar Coat Green XSFurCollarCoatGreenFast Fashion Womens Old0
NL2785 Ladies Parka Khaki 8NL2785ParkaKhakiFast Fashion Womens Old0
NL2785 Ladies Parka Khaki 10NL2785ParkaKhakiFast Fashion Womens Old0
NL2785 Ladies Parka Khaki 12NL2785ParkaKhakiFast Fashion Womens Old0
NL2785 Ladies Parka Khaki 14NL2785ParkaKhakiFast Fashion Womens Old2

<tbody>
</tbody>
 

J.Ty.

Well-known Member
Joined
Feb 4, 2012
Messages
850
Office Version
365, 2016, 2010
Platform
Windows, Web
Hi,

Please test the following solution:

<b>Excel 2016 (Windows) 64 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">KA3076 Dress 32849 Rasberry/Cream S</td><td style=";">KA3076Dress32849Rasb/Cream</td><td style=";">Glamorous Old</td><td style="text-align: right;;">2</td><td style=";">brand</td><td style=";"># of styles</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">KA3076 Dress 32849 Rasberry/Cream M</td><td style=";">KA3076Dress32849Rasb/Cream</td><td style=";">Glamorous Old</td><td style="text-align: right;;">-1</td><td style=";">Fast Fashion Womens Old</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">KA3076 Dress 32849 Rasberry/Cream L</td><td style=";">KA3076Dress32849Rasb/Cream</td><td style=";">Glamorous Old</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">CK0550 Dress 32029 Rust XS</td><td style=";">CK0550Dress32029Rust</td><td style=";">Glamorous Old</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">CK0550 Dress 32029 Rust S</td><td style=";">CK0550Dress32029Rust</td><td style=";">Glamorous Old</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">CK0550 Dress 32029 Rust M</td><td style=";">CK0550Dress32029Rust</td><td style=";">Glamorous Old</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">CK0550 Dress 32029 Rust L</td><td style=";">CK0550Dress32029Rust</td><td style=";">Glamorous Old</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">1393 Ladies dress-32322 Black/mustard XS</td><td style=";">AN1393dress-32322Blk/mustard</td><td style=";">Glamorous Old</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">N1393 Ladies dress-32322 Black/mustard S</td><td style=";">AN1393dress-32322Blk/mustard</td><td style=";">Glamorous Old</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">N1393 Ladies dress-32322 Black/mustard M</td><td style=";">AN1393dress-32322Blk/mustard</td><td style=";">Glamorous Old</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">N1393 Ladies dress-32322 Black/mustard L</td><td style=";">AN1393dress-32322Blk/mustard</td><td style=";">Glamorous Old</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Fur Collar Coat Green S</td><td style=";">FurCollarCoatGreen</td><td style=";">Fast Fashion Womens Old</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Fur Collar Coat Green M</td><td style=";">FurCollarCoatGreen</td><td style=";">Fast Fashion Womens Old</td><td style="text-align: right;;">-1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Fur Collar Coat Green L</td><td style=";">FurCollarCoatGreen</td><td style=";">Fast Fashion Womens Old</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">Fur Collar Coat Green XL</td><td style=";">FurCollarCoatGreen</td><td style=";">Fast Fashion Womens Old</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">Fur Collar Coat Green XS</td><td style=";">FurCollarCoatGreen</td><td style=";">Fast Fashion Womens Old</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">NL2785 Ladies Parka Khaki 8</td><td style=";">NL2785ParkaKhaki</td><td style=";">Fast Fashion Womens Old</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">NL2785 Ladies Parka Khaki 10</td><td style=";">NL2785ParkaKhaki</td><td style=";">Fast Fashion Womens Old</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">NL2785 Ladies Parka Khaki 12</td><td style=";">NL2785ParkaKhaki</td><td style=";">Fast Fashion Womens Old</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">NL2785 Ladies Parka Khaki 14</td><td style=";">NL2785ParkaKhaki</td><td style=";">Fast Fashion Womens Old</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F2</th><td style="text-align:left">{=SUM(<font color="#0000FF">(<font color="#FF0000">$D$1:$D$20>0</font>)*IFERROR(<font color="#FF0000">1/COUNTIFS(<font color="#00FF00">$C$1:$C$20,E2,$D$1:$D$20,">0",$B$1:$B$20,$B$1:$B$20</font>),0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Forum statistics

Threads
1,081,703
Messages
5,360,747
Members
400,595
Latest member
T_Dubs

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top