Basic Search ISNUMBER/LEN formula help!

Lukums

Board Regular
Joined
Nov 23, 2015
Messages
195
[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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
You do not specify the expected counts for the sample you posted. That said...



Book1
ABCDE
1ProductsColour250027704600410030003150
22x30003150, 2xMRC2562, PAD2500,RED111
34x46004100, 2xmrcBLUE
44x30003150, 4xmrc, PAD250RED
55x25002770Cream
62x25002700Cream
Sheet1


In C2 enter and copy across:

=COUNTIFS($A$2:$A$6,"*"&$C$1&"*")
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Use COUNTIFS and add column B as the extra criterion.
 
Upvote 0
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...
 
Upvote 0
=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. ;)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,187
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top