Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Sum Range With Part Text In Another Range

1. I agreed to these rules
Join Date
7th November 2006
Posts
14

## Sum Range With Part Text In Another Range

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 :/

Excel Video Tutorials / Excel Dashboards Reports

2. Super Moderator
Join Date
7th December 2005
Location
Hampshire, England
Posts
4,898

## Re: Sum With Similar Text Criteria

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.

Excel Video Tutorials / Excel Dashboards Reports

3. ## Re: Sum With Similar Text Criteria

use a formula something like this

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

Excel Video Tutorials / Excel Dashboards Reports

4. I agreed to these rules
Join Date
7th November 2006
Posts
14

## Re: Sum With Similar Text Criteria

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...

Excel Video Tutorials / Excel Dashboards Reports

5. ## Re: Sum With Similar Text Criteria

Does this od what you are after?

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

6. ## Re: Sum With Similar Text Criteria

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
Last edited by Dave Hawley; December 13th, 2006 at 19:34.

Excel Video Tutorials / Excel Dashboards Reports

7. Super Moderator
Join Date
7th December 2005
Location
Hampshire, England
Posts
4,898

## Re: Sum With Similar Text Criteria

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!

Excel Video Tutorials / Excel Dashboards Reports

8. ## Re: Sum With Similar Text Criteria

You can use Wildcards in SUMIF

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

9. I agreed to these rules
Join Date
7th November 2006
Posts
14

## Re: Sum With Similar Text Criteria

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...

Excel Video Tutorials / Excel Dashboards Reports

10. ## Re: Sum Range With Part Text In Another Range

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

Excel Video Tutorials / Excel Dashboards Reports

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno