search for text and if condition return value

[orsm6]

Hi all - i've tried a couple of adaptations of a formula i was given here, but it isn't doing what i need to do.

column B holds numbers: 1 - 5
column D holds cells with text
Column H holds quantities for each of the text cells in column D

I am trying to use a static cell: U8 to search column D for the text CURD and if column B in same row as curd = 2 return the value on the same row in H

hope this makes sense. I can't use a formula to drag it down alongside the source data as i need to build a summary sheet.

TIA

 

[jtakw]

hahah this is strange, I don't always see your replies.....

What do you mean, I've replied to your post(s) before...as far as I remember...

Anyway, I'm off to make dinner soon, let's get this wrapped up for you.

Probably a good idea for you to show some possible sentences and expected results.

 

[orsm6]

What do you mean, I've replied to your post(s) before...as far as I remember...

Anyway, I'm off to make dinner soon, let's get this wrapped up for you.

Probably a good idea for you to show some possible sentences and expected results.

I can't show examples of text as it is data that shouldn't be shared.

But to answer, yes in column D any of the cells could contain curd, and it could appear more than once in this column.

The same applies with column be where it would have 2.

If there are multiples found, there is only ever one of them that will have a qty against it. The other is blank or 0

When I say multiple. One row might have 2 in B then curd moisture and qty

The other row might have 2 in B phantom curd and 0 qty

 

[jtakw]

I mean some dummy-up data.

 

[jtakw]

Hey, in the mean time, see if this works for your data:

Book3.xlsx
	B	C	D	E	F	G	H	I	J
11	Lv	Obj	Object description		Ict	Item	Qty (CUn)		Result
12
13	1	20064260	text		L	10	1000		0.002
14	1	20061449	text		L	20	8000
15	1	PA021700	text		L	30	2000
16	1	PA025079	text		L	40	8000
17	1	PA027596	text		L	50	14
18	1	ZX000184	text		N	60	1088
19	1	ZX000184	text
20	2	WA000053	this curd		L	20
21	2	WA003332	curd that		L	30	0
22	2	WA003332	text curd text		L	40	0.002
23	2	ZX000024	CURD text text		N	50
24	2	SA000450	text		L	60	476.989
25	2	RA000237	text text curd		L	70	0
26	2	RA001003	text		L	80	-1.088
27	2	RA001003	text		L	90	1.088
Sheet868

Cell Formulas
Range	Formula
J13	J13	=SUMPRODUCT((ISNUMBER(SEARCH("CURD",D13:D30))*(B13:B30=2)*(H13:H30<>"")*(H13:H30<>0)*H13:H30))

 

[orsm6]

Hey, in the mean time, see if this works for your data:

Book3.xlsx
	B	C	D	E	F	G	H	I	J
11	Lv	Obj	Object description		Ict	Item	Qty (CUn)		Result
12
13	1	20064260	text		L	10	1000		0.002
14	1	20061449	text		L	20	8000
15	1	PA021700	text		L	30	2000
16	1	PA025079	text		L	40	8000
17	1	PA027596	text		L	50	14
18	1	ZX000184	text		N	60	1088
19	1	ZX000184	text
20	2	WA000053	this curd		L	20
21	2	WA003332	curd that		L	30	0
22	2	WA003332	text curd text		L	40	0.002
23	2	ZX000024	CURD text text		N	50
24	2	SA000450	text		L	60	476.989
25	2	RA000237	text text curd		L	70	0
26	2	RA001003	text		L	80	-1.088
27	2	RA001003	text		L	90	1.088
Sheet868

Cell Formulas
Range	Formula
J13	J13	=SUMPRODUCT((ISNUMBER(SEARCH("CURD",D13:D30))*(B13:B30=2)*(H13:H30<>"")*(H13:H30<>0)*H13:H30))

thank you again - this one is working so far also
appreciate your patience and help for sure.

sorry for slow reply, I also shot out for something to eat.

 

[orsm6]

Hey, in the mean time, see if this works for your data:

Book3.xlsx
	B	C	D	E	F	G	H	I	J
11	Lv	Obj	Object description		Ict	Item	Qty (CUn)		Result
12
13	1	20064260	text		L	10	1000		0.002
14	1	20061449	text		L	20	8000
15	1	PA021700	text		L	30	2000
16	1	PA025079	text		L	40	8000
17	1	PA027596	text		L	50	14
18	1	ZX000184	text		N	60	1088
19	1	ZX000184	text
20	2	WA000053	this curd		L	20
21	2	WA003332	curd that		L	30	0
22	2	WA003332	text curd text		L	40	0.002
23	2	ZX000024	CURD text text		N	50
24	2	SA000450	text		L	60	476.989
25	2	RA000237	text text curd		L	70	0
26	2	RA001003	text		L	80	-1.088
27	2	RA001003	text		L	90	1.088
Sheet868

Cell Formulas
Range	Formula
J13	J13	=SUMPRODUCT((ISNUMBER(SEARCH("CURD",D13:D30))*(B13:B30=2)*(H13:H30<>"")*(H13:H30<>0)*H13:H30))

I have now been told that they want this formula sitting on another sheet.
what's difficult is that the sheet names are going to be dynamic... but there is a cell that holds the sheet name.

is there a way to incorporate INDIRECT into your formula? i have been trying to do it, just ending up getting 0 as results.

 

[jtakw]

Assuming A1 holds the sheet name, use

=INDIRECT(A1&"!Cell/Range reference")

Insert into formula where needed.

 

[orsm6]

Would I only need the indirect reference once or at every range contained in the formula?

 

[jtakw]

You need to add INDIRECT to Every Cell and Range reference that's on a different sheet.