# Basic Search ISNUMBER/LEN formula help!

#### Lukums

##### Board Regular
 Products Colour Number of Smooth Doors = 25002770 Number of Blue Doors = 46004100 Number of Red Doors = 30003150 2x30003150, 2xMRC2562, PAD2500, RED 7 4 6 4x46004100, 2xmrc BLUE 4x30003150, 4xmrc, PAD250 RED 5x25002770 Cream 2x25002700 Cream

<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.

### 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.

##### MrExcel MVP
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
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
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

Use COUNTIFS and add column B as the extra criterion.

#### Lukums

##### Board Regular
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

=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
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

##### MrExcel MVP
Maybe like this:

=SUMPRODUCT(COUNTIFS(\$A\$2:\$A\$30,"*"&ROW(\$1:\$100)&"xInso*")*ROW(\$1:\$100))

Replies
2
Views
262
Replies
0
Views
108