# search for text and if condition return value

#### orsm6

##### Active Member
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

##### Well-known Member
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.

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### orsm6

##### Active Member
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

##### Well-known Member
I mean some dummy-up data.

#### jtakw

##### Well-known Member
Hey, in the mean time, see if this works for your data:

Book3.xlsx
BCDEFGHIJ
11LvObjObject descriptionIctItem Qty (CUn)Result
12
13120064260textL1010000.002
14120061449textL208000
151PA021700textL302000
161PA025079textL408000
171PA027596textL5014
181ZX000184textN601088
191ZX000184text
202WA000053this curdL20
212WA003332curd thatL300
222WA003332text curd textL400.002
232ZX000024CURD text textN50
242SA000450textL60476.989
252RA000237text text curdL700
262RA001003textL80-1.088
272RA001003textL901.088
Sheet868
Cell Formulas
RangeFormula
J13J13=SUMPRODUCT((ISNUMBER(SEARCH("CURD",D13:D30))*(B13:B30=2)*(H13:H30<>"")*(H13:H30<>0)*H13:H30))

#### orsm6

##### Active Member

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

Book3.xlsx
BCDEFGHIJ
11LvObjObject descriptionIctItem Qty (CUn)Result
12
13120064260textL1010000.002
14120061449textL208000
151PA021700textL302000
161PA025079textL408000
171PA027596textL5014
181ZX000184textN601088
191ZX000184text
202WA000053this curdL20
212WA003332curd thatL300
222WA003332text curd textL400.002
232ZX000024CURD text textN50
242SA000450textL60476.989
252RA000237text text curdL700
262RA001003textL80-1.088
272RA001003textL901.088
Sheet868
Cell Formulas
RangeFormula
J13J13=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

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

Book3.xlsx
BCDEFGHIJ
11LvObjObject descriptionIctItem Qty (CUn)Result
12
13120064260textL1010000.002
14120061449textL208000
151PA021700textL302000
161PA025079textL408000
171PA027596textL5014
181ZX000184textN601088
191ZX000184text
202WA000053this curdL20
212WA003332curd thatL300
222WA003332text curd textL400.002
232ZX000024CURD text textN50
242SA000450textL60476.989
252RA000237text text curdL700
262RA001003textL80-1.088
272RA001003textL901.088
Sheet868
Cell Formulas
RangeFormula
J13J13=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

##### Well-known Member

Assuming A1 holds the sheet name, use

Excel Formula:
``=INDIRECT(A1&"!Cell/Range reference")``

Insert into formula where needed.

#### orsm6

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

#### jtakw

##### Well-known Member
You need to add INDIRECT to Every Cell and Range reference that's on a different sheet.

Replies
7
Views
150
Replies
2
Views
60
Replies
4
Views
239
Replies
3
Views
78
Replies
4
Views
80

1,130,294
Messages
5,641,392
Members
417,207
Latest member
Vxhaet

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

### Which adblocker are you using?

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

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