Basic Search ISNUMBER/LEN formula help!

[Lukums]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Products[/TD]
[TD]Colour[/TD]
[TD]Number of Smooth Doors = 25002770[/TD]
[TD]Number of Blue Doors = 46004100[/TD]
[TD]Number of Red Doors = 30003150[/TD]
[/TR]
[TR]
[TD]2x30003150, 2xMRC2562, PAD2500,[/TD]
[TD]RED[/TD]
[TD]7[/TD]
[TD]4[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]4x46004100, 2xmrc[/TD]
[TD]BLUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4x30003150, 4xmrc, PAD250[/TD]
[TD]RED[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5x25002770[/TD]
[TD]Cream[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2x25002700[/TD]
[TD]Cream[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Aladin Akyurek]

You do not specify the expected counts for the sample you posted. That said...

Book1
	A	B	C	D	E
1	Products	Colour	25002770	46004100	30003150
2	2x30003150, 2xMRC2562, PAD2500,	RED	1	1	1
3	4x46004100, 2xmrc	BLUE
4	4x30003150, 4xmrc, PAD250	RED
5	5x25002770	Cream
6	2x25002700	Cream
Sheet1

In C2 enter and copy across:

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

 

[RoryA]

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]

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]

Use COUNTIFS and add column B as the extra criterion.

 

[Lukums]

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]

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

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

 

[Aladin Akyurek]

Maybe like this:

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