search for text and if condition return value

orsm6

Active Member
Joined
Oct 3, 2012
Messages
496
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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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
 
Upvote 0
Hi,

The Texts in column D, are they Single words, or phrase (sentences)?
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

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