Searching for a string, return sum

[ComradeBT]

This can't be a difficult one but I'm having a memory blank!

I have several cells in Column A containing text. In Column B the cell contains a value. I wish to return the sum of the total value if Column A contains a specific word. In other words, I need to search Column A for a string, if that is found return the sum of the values in Column B.

Hope someone can advise and prompt my mind back into action!

BT.

[ChemistB]

So the string in Col A can be part of a string? i.e.
Housefly---- 12
House------ 4
In the House--2
living room ---10
will return 18 or just 2?

[ComradeBT]

It could be any part of the string, so in your example it would need to return the sum of 18.

[Harley2]

Assuming the above example are in column "A" and "B" then this should work
=SUM(--(IFERROR(IF(SEARCH("house",A3:A6),1,0),0))*(B3:B6)) and enter as an array formula with CTRL, Shift, Enter.

[ChemistB]

This should work also
=SUMPRODUCT(--(ISNUMBER(SEARCH("house",$A$1:$A$4000))),$B$1:$B$4000)

It does not require being entered as an Array.

[ComradeBT]

Thanks for trying to help as well. I have copied your formula and just changed where necessary but no value is returned. Would it cause a problem if the values I am trying to return are formatted as a currency in the cell rather than a number?

Ben.

[ChemistB]

Being formatted as currancy should not affect the formula. Without an example workbook (enter dummy data if necessary), it's hard to troubleshoot. (Go advanced and "Manage Attachments").

One thought, if you modified the formula. the ranges must be the same size (i.e. can't have A2:A4000 and B2:B1000).

[ComradeBT]

Attached is a sub-set of what I'm trying to work on.

[martindwilson]

err what value/string you looking for?
but
=SUMIF(A2:A5,"*"&D1&"*",B2:B4) where d1 contains word pennant would give
10.93

[ComradeBT]

I'm looking to return the sum of the price in the second column where the string in the first column contains the word 'pennant'.

[martindwilson]

see my last post
or just
=SUMIF(A2:A5,"*pennant*",B2:B4)
@chemistb i thought you were tied up on that other thread lol

[ChemistB]

LOL, I have to get up and walk around occasionally.

[ComradeBT]

Thanks for looking into this Martin.

I've tried both of your formulas. The first just returns a '#NAME?' error.
The second example you provided has returned '0' this time. So no error is coming up but at the same time the sum doesn't seem to be calculating and returning any value. Any idea??

Just to clarify, the word 'pennant' could be located anywhere within the cells that I'm searching for in Column A.

Ben.

[ChemistB]

#Name error probably means your quotations are not set properly? Your text needs to be in double quotes.

[ComradeBT]

I've set double quotes around the word like so:

=SUMIF(A2:A133,'*'&pennant&'*',B2:B133)

but it returns:

#NAME?

[ChemistB]

=SUMIF(A2:A133,"*pennant*",B2:B133)

[ComradeBT]

Ok, that doesn't return an error now. However, it still only returns zero (0) and not the sum of the values where the word 'pennant' occurs.

This is weird!!

[ChemistB]

See attachment

[ComradeBT]

Thanks for that. I've taken your example and amended it slightly but the function still doesn't return a value. Attached is what I've done - you will see that each cell just returns £0.00.

Any thoughts?

[martindwilson]

not when i open it it shows £34.53

[ComradeBT]

Ok, I think I've tied the problem down now.
I'm using Open Office to work .xls files and there must be a different character used instead of the *

So in the example below I need to replace the * with something else. It's not a '%' sign though!!
=SUMIF(A2:A133,"*pennant*",B2:B133)


I'll have a search to see if I can find out what to use.
Thanks for all your help!

[martindwilson]

Dont you think you should have mentioned open office?. Calc is not the same as excel
calc uses regular expressions and wild card is .* to get it to work you need
=SUMIF(A$2:A$13;".*pennant.*";B$2:B$13) then go to tools/options/openoffice.orgcalc/calculate
and check enable regular expressions in formulae click ok

[ComradeBT]

Many thanks for all your help Martin. It's much appreciated and apologies for not being clear from the outset.