Basic Search ISNUMBER/LEN formula help!

Lukums

Board Regular
Joined
Nov 23, 2015
Messages
195
ProductsColourNumber of Smooth Doors = 25002770Number of Blue Doors = 46004100Number of Red Doors = 30003150
2x30003150, 2xMRC2562, PAD2500,RED746
4x46004100, 2xmrcBLUE
4x30003150, 4xmrc, PAD250RED
5x25002770Cream
2x25002700Cream

<tbody>
</tbody>

Hello, just need a basic (hopefully formula which would work out the above)]

In the product column there could be a whole range of products - however we only care about 30003150, 46004100, 25002770.

We need to find the amount of doors found by 2x ( with these products only ) - for exmaple of any amount found 1x,2x,3x,4x,5x,6x,7x,8x

Totalling in column C/D/E (row 2 is fine)

Any ideas? ISNUMBER/LEN/ I tried so much I just can't work it out.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
You do not specify the expected counts for the sample you posted. That said...


<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">Products</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">Colour</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">25002770</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">46004100</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">30003150</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">2x30003150, 2xMRC2562, PAD2500,</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">RED</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">4x46004100, 2xmrc</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">BLUE</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">4x30003150, 4xmrc, PAD250</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">RED</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">5x25002770</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">Cream</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">2x25002700</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">Cream</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p>

In C2 enter and copy across:

=COUNTIFS($A$2:$A$6,"*"&$C$1&"*")
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,520
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You could use something like this:

=SUM(COUNTIF(A2:A6,"*"&{1,2,3,4,5,6,7,8,9}&"x30003150*")*{1,2,3,4,5,6,7,8,9})

Adjust ranges and the code to look for as needed. If you could have more than 9 of something, the formula would need tweaking.
 

Lukums

Board Regular
Joined
Nov 23, 2015
Messages
195
You could use something like this:

=SUM(COUNTIF(A2:A6,"*"&{1,2,3,4,5,6,7,8,9}&"x30003150*")*{1,2,3,4,5,6,7,8,9})

Adjust ranges and the code to look for as needed. If you could have more than 9 of something, the formula would need tweaking.


Super close but not quite.

You're summing the totals which is perfect against x30003150 but I also need to look at the colour found in B column.

So technically Red doors of x30003150 should be "6".

Currently this totals fine! but I have no idea how to add the additional criteria
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,520
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Use COUNTIFS and add column B as the extra criterion.
 

Lukums

Board Regular
Joined
Nov 23, 2015
Messages
195
Use COUNTIFS and add column B as the extra criterion.


This is why I do everything in VBA... I hate these formulas! -

SUM(COUNTIFS(B2:B20,"Heritage Red",(A2:A20,"*"&{1,2,3,4,5,6,7,8,9}&"x30003150*")*{1,2,3,4,5,6,7,8,9})

I can't work it out...
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,520
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

=SUM(COUNTIFS(B2:B20,"Heritage Red",A2:A20,"*"&{1,2,3,4,5,6,7,8,9}&"x30003150*")*{1,2,3,4,5,6,7,8,9})

is what I meant. ;)
 

sx200apul

New Member
Joined
Sep 17, 2018
Messages
5
Any ideas??

=SUM(COUNTIFS(A2:A30,"*"&{1,2,3,4,5,6,7,8,9}&"xInso*")*{1,2,3,4,5,6,7,8,9})

this works for quantity 1-9, but what if the quantity is between 1-100?

how can i do this using the existing formula
 

Watch MrExcel Video

Forum statistics

Threads
1,109,450
Messages
5,528,818
Members
409,838
Latest member
Cowells01
Top