Sum of numeric String with Condition

Hello Excel Experts,

I was able to get help, and just realized that I need to put a condition.

Given 5 rows in column A, I have strings that has the word 'Qty' and some none. For any string that has the word 'Qty', I want the numeric value before it. For string that doesn't have the word 'Qty', I want it to be 1.

In column B, I have categories, such as "Excel", "PowerPoint" etc.

My question is, how should I added the numeric string for Excel without a helper column?

Column A	Column B	Comment
some string name and ended with (4 Qty)	PowerPoint	not to take (not Excel)
some string name WITHOUT the word Qty	Excel	to take numeric 1 BY DEFAULT even though there's no 'Qty' (Excel)
some string name and ended with (12 Qty)	Excel	to take numeric 12 (Excel)
some string name and ended with (3 Qty)	Excel	to take numeric string 3 (Excel)
some string name and ended with (5 Qty)	Word	not to take (not Excel)

In the above example, I would like to have an answer in say cell C1 to be 16

Edit: People assume that I wanted to extract the string numeric to each respective cell on column C, WHICH IS NOT, thus, I'm taking it out to have the value that I want in cell C1 to be 6

Perhaps

Please Login or Register  to view this content.

Assuming Quantity is always < 10
=IF(AND(B2="Excel", ISERROR(Search("QTY", A2))), 1,IF(AND(B2="Excel", ISNUMBER(Search("qty", A2))),MID(A2, Search("qty", A2)-2,1)+0, 0))

And will there ever be other digits/numbers in your text. I'm thinking maybe a UDF to extract the number.

May be this......

Please Login or Register  to view this content.

Is this what you are trying to achieve?

I'm stumped. Nothing I've tried works.

Hi,

Have I missed something, or has an actual workbook yet to be uploaded as part of this thread? If not, sounds like it's badly needed.

Regards

Originally Posted by dluhut

Sample attached...which is practically the same as what I've done in the 1st thread, but make it into a tabular form

Can't see it (?)

Regards

Originally Posted by Pete_UK

It's attached to the first post.

Hope this helps.

Pete

Ah! Thanks, Pete.

Regards

So if a quantity does exist in the string, it is always contained within parentheses at the very end of the string followed by a space and the word "Qty"?

Regards

well this works array entered
=SUMPRODUCT((IF(ISNUMBER(--SUBSTITUTE(MID(A1:A5,SEARCH("(*)",A1:A5)+1,10)," Qty)","")),--SUBSTITUTE(MID(A1:A5,SEARCH("(*)",A1:A5)+1,10)," Qty)",""),1)),--(B1:B5="excel"))

Originally Posted by martindwilson

well this works array entered
=SUMPRODUCT((IF(ISNUMBER(--SUBSTITUTE(MID(A1:A5,SEARCH("(*)",A1:A5)+1,10)," Qty)","")),--SUBSTITUTE(MID(A1:A5,SEARCH("(*)",A1:A5)+1,10)," Qty)",""),1)),--(B1:B5="excel"))

SUMPRODUCT in an array formula?! Ouch!

Try this array formula**:

=SUM(IF(ISNUMBER(SEARCH("qty",A2:A6)),IF(B2:B6="Excel",--SUBSTITUTE(MID(A2:A6,FIND("(",A2:A6)+1,20)," Qty)",""))))+COUNTIFS(A2:A6,"<>",A2:A6,"<>*Qty*",B2:B6,"Excel")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Might as well put my two cents' worth in. Array formula**:

=-SUM((B2:B6="Excel")*(IFERROR(0+TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A2:A6,"Qty","")," ",REPT(" ",MAX(LEN(A2:A6)))),2*MAX(LEN(A2:A6)))),-1)))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Originally Posted by martindwilson

well this works array entered
=SUMPRODUCT((IF(ISNUMBER(--SUBSTITUTE(MID(A1:A5,SEARCH("(*)",A1:A5)+1,10)," Qty)","")),--SUBSTITUTE(MID(A1:A5,SEARCH("(*)",A1:A5)+1,10)," Qty)",""),1)),--(B1:B5="excel"))

Empty cells could cause an incorrect result.

yep a blank in col a gives 1 but it prompted you lot to get of your butts and have a go

Originally Posted by XOR LX

=-SUM((B2:B6="Excel")*(IFERROR(0+TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A2:A6,"Qty","")," ",REPT(" ",MAX(LEN(A2:A6)))),2*MAX(LEN(A2:A6)))),-1)))

No empty cells in the sample data but if there were that formula could return an incorrect result.

Originally Posted by Tony Valko

No empty cells in the sample data but if there were that formula could return an incorrect result.

True. Should've considered it as a possibility. Getting late here!

Cheers

=SUM(IF(B2:B6="Excel",IFERROR(SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A2:A6," ",REPT(" ",255),LEN(A2:A6)-LEN(SUBSTITUTE(A2:A6," ",""))-1),255)),"(",""),"Qty)","")+0,1)))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

	A	B	C
1	String with Quantity Headers	Categories	Desired Answer in Cell C2
2	some string name and ended with (4 Qty)	PowerPoint	16
3	some string name WITHOUT the word Qty	Excel
4	some string name and ended with (12 Qty)	Excel
5	some string name and ended with (3 Qty)	Excel
6	some string name and ended with (5 Qty)	Word

id assume a)that cell wont be empty
but b) if it was it wouldn't have excel in col b
and also assume that the string in a didn't contain any more () like
"some string (this is ver 1) of my stuff (12 Qty)"
so if the both mine and x or xls would fail with empty cells in col a
x or xls works with additional () tho
tony v works with empty cells but fails as so does mine with extra()
lots of assumptions to be made on the raw data here

The thing about formulas...

Almost any formula will fail if you try hard enough.

you have had several suggestions already have you tried them? if so which work ,which don't?