search for text and if condition return value

orsm6

Active Member
Joined
Oct 3, 2012
Messages
383
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Oct 3, 2012
Messages
383
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
I mean some dummy-up data.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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))
 
Solution

orsm6

Active Member
Joined
Oct 3, 2012
Messages
383
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Oct 3, 2012
Messages
383
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Assuming A1 holds the sheet name, use

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

Insert into formula where needed.
 

orsm6

Active Member
Joined
Oct 3, 2012
Messages
383
Office Version
  1. 365
Platform
  1. Windows
Would I only need the indirect reference once or at every range contained in the formula?
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
You need to add INDIRECT to Every Cell and Range reference that's on a different sheet.
 

Watch MrExcel Video

Forum statistics

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