Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 15

Thread: Sum Range With Part Text In Another Range

  1. #1
    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 :/

    thanx in advance

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    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. #3
    Join Date
    26th July 2004
    Posts
    1,335

    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. #4
    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...
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    28th June 2005
    Location
    Lincolnshire, UK
    Posts
    1,137

    Re: Sum With Similar Text Criteria

    Does this od what you are after?

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

    www.imits.co.uk - for all your training and development needs!

  6. #6
    Join Date
    26th July 2004
    Posts
    1,335

    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. #7
    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. #8
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,709

    Re: Sum With Similar Text Criteria

    You can use Wildcards in SUMIF

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

  9. #9
    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. #10
    Join Date
    26th July 2004
    Posts
    1,335

    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


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Find Text In Range With Part of Text
    By oonz in forum EXCEL HELP
    Replies: 6
    Last Post: October 1st, 2011, 07:04
  2. Count Instances Of Part Cell Text In Range
    By Nyanko in forum EXCEL HELP
    Replies: 2
    Last Post: January 22nd, 2008, 09:49
  3. Is Named Range Part Of Another Range
    By Zerothi in forum EXCEL HELP
    Replies: 2
    Last Post: June 29th, 2007, 18:52
  4. Replies: 9
    Last Post: September 28th, 2006, 00:04
  5. Replace Part of Text In Range
    By Lippa in forum EXCEL HELP
    Replies: 4
    Last Post: August 26th, 2006, 01:44

Bookmarks

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