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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

orsm6

Active Member
Joined
Oct 3, 2012
Messages
383
Office Version
  1. 365
Platform
  1. Windows
here is an image. formula in U8 should look for text CURD, that there is a 2 and return the qty.

Schedule Builder.xlsm
BCDEFGH
11LvObjObject descriptionIctItem Qty (CUn)
12
13120064260textL101,000
14120061449textL208,000
151PA021700textL302,000
161PA025079textL408,000
171PA027596textL5014
181ZX000184textN601,088
191ZX000184text
202WA000053textL200.002
212WA003332textL30-0.001
222WA003332textL400.002
232ZX000024CURDN50432.471
242SA000450textL60476.989
252RA000237textL70139.282
262RA001003textL80-1.088
272RA001003textL901.088
4026443
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

The Texts in column D, are they Single words, or phrase (sentences)?
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Book3.xlsx
BCDEFGHIJ
11LvObjObject descriptionIctItem Qty (CUn)Result
12
13120064260textL101000432.471
14120061449textL208000
151PA021700textL302000
161PA025079textL408000
171PA027596textL5014
181ZX000184textN601088
191ZX000184text
202WA000053textL200.002
212WA003332textL30-0.001
222WA003332textL400.002
232ZX000024CURDN50432.471
242SA000450textL60476.989
252RA000237textL70139.282
262RA001003textL80-1.088
272RA001003textL901.088
Sheet868
Cell Formulas
RangeFormula
J13J13=SUMIFS(H13:H100,D13:D100,"CURD",B13:B100,2)


Edit: Happened again.

So you want the Total of H for all instances where the word "CURD" is found with a corresponding 2 in the same row?
 
Last edited:

orsm6

Active Member
Joined
Oct 3, 2012
Messages
383
Office Version
  1. 365
Platform
  1. Windows
I was posting when you were posting, try:

Book3.xlsx
BCDEFGHIJ
11LvObjObject descriptionIctItem Qty (CUn)Result
12
13120064260textL101000432.471
14120061449textL208000
151PA021700textL302000
161PA025079textL408000
171PA027596textL5014
181ZX000184textN601088
191ZX000184text
202WA000053textL200.002
212WA003332textL30-0.001
222WA003332textL400.002
232ZX000024CURDN50432.471
242SA000450textL60476.989
252RA000237textL70139.282
262RA001003textL80-1.088
272RA001003textL901.088
Sheet868
Cell Formulas
RangeFormula
J13J13=SUMIFS(H13:H100,D13:D100,"CURD",B13:B100,2)
Oh wow, that simple. I was wayyyyyyy overthinking the complexity of this one.
It does indeed so far work, i'll test it for a while and see how I go.

well done and thank you
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

sentences. CURD could be anywhere in it

If that's the case my formula posted above wouldn't work, please clarify.
 

orsm6

Active Member
Joined
Oct 3, 2012
Messages
383
Office Version
  1. 365
Platform
  1. Windows
Book3.xlsx
BCDEFGHIJ
11LvObjObject descriptionIctItem Qty (CUn)Result
12
13120064260textL101000432.471
14120061449textL208000
151PA021700textL302000
161PA025079textL408000
171PA027596textL5014
181ZX000184textN601088
191ZX000184text
202WA000053textL200.002
212WA003332textL30-0.001
222WA003332textL400.002
232ZX000024CURDN50432.471
242SA000450textL60476.989
252RA000237textL70139.282
262RA001003textL80-1.088
272RA001003textL901.088
Sheet868
Cell Formulas
RangeFormula
J13J13=SUMIFS(H13:H100,D13:D100,"CURD",B13:B100,2)


Edit: Happened again.

So you want the Total of H for all instances where the word "CURD" is found with a corresponding 2 in the same row?
No I don't want the total for all cells containing curd. just the qty where curd is found and 2 is in column.

I tried this, the formula you gave me works but only if the cell contains just the text CURD....

i need to FIND the text CURD... which is in sentences within the cell.
 

orsm6

Active Member
Joined
Oct 3, 2012
Messages
383
Office Version
  1. 365
Platform
  1. Windows
hahah this is strange, I don't always see your replies.....

The cells in D have descritptions that may have many words in one cell. one word could be CURD. so formula needs to find it, plus the other condition.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Can there be more than 1 row where the "sentence" in D column contains "CURD", and B column contains "2"?
If so, what do you want as result? The Total, or the First found, or Last found?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,536
Messages
5,636,887
Members
416,947
Latest member
asher_nk

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