Sum Range With Part Text In Another Range

[wydra]

Hi everybody!

Great forum, learned a lot of things about excel I didn't know before

My problem is this, I have to group some data from a pdf format.
I import them into a excel worksheet, but the problem is with selecting them, because the data are really messed up. I tried selecting them with sumproduct with criteria, but the problem is that the formula wants the criteria to be a text that is in only one cell, whereas in my case there I would like the formula to select for example the cell that has the text "Visa34" as well as the cell with "Visa12,FIB3" (i.e. all the cells with "Visa" even thought there might be other words or letters in the same cell...). Can somebody help me? I would really be grateful, cause till now I (and the others before me) used to just print the data and write them down in excel... I really think there can be a lot of easier ways than that :/

thanx in advance

[ByTheCringe2]

Welcome to the forum!

Have you tried using LEFT(A1,4) in your formulas? If you could attach a cut-down sample workbook showing your reuirements, that would help.

[pangolin]

use a formula something like this

=SUMPRODUCT((LEFT(A1:A3,4)="Visa")*(B1:B3))

[wydra]

hello! thanx for the very quick reply

I just tried the formula with LEFT, but didn't seem to work... I've attached an example of what I am talking about, and in that mess I have to select data with more than one vague criteria, i.e. sum all the cells that are in a row with the cell with a "FINVALTR" in it, but also with a "Visa", and so on. The problem is that there is only one cell with the criteria and description, and when I try and use 'text to columns', it doesnt' help much, cause not always the text has spaces between words...

[Badger101]

Does this od what you are after?

=SUMPRODUCT(--ISNUMBER(SEARCH("visa",B1:B17))*D1:D17)

[pangolin]

use this formula

=SUMPRODUCT(IF(ISERROR(FIND("VISA",UPPER(B1:B17))),0,1)*IF(ISERROR(FIND("FINVALTR",UPPER(B1:B17))),0,1)*(D1:D17))

To Enter the array formula hold down Ctrl and Shift while pushing Enter.

it gives me a value of 28790

[ByTheCringe2]

Hey, Pangolin, I just wrote my first array formula, very similar to yours, but I didn't realise it was one until I saw your post! Thanks!

[Dave Hawley]

You can use Wildcards in SUMIF

SUMIF($B$1:$B$17,"*Visa*",$D$1:$D$17))

[wydra]

hi again and thx again

I tried your formula pangolin and it worked perfectly, but when I tried changing the criteria (i.e. instead of "Visa" put "Buxheti" or "Fonde" and instead of "FINVALTR" put "CDISALTR", the formula doesn't work anymore...

how come in some cases it works and in some not? Maybe there is something I don't know about it...

P.S. I did use the ctrl+alt+enter...

[pangolin]

try putting everything in capitals

for eg "Fonde" should be put as FONDE...see the UPPER function in the formula which converts the string into upper case and VISA and FINVALTR are in caps

that was done because some words are in upper and some in lower

also if an array formula is modified then the "curly" brackets disappear and the formula needs to be again confirmed by Ctrl+Shift+Enter