Announcement

Collapse
No announcement yet.

Sum Range With Part Text In Another Range

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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.
    .

    Comment


    • #3
      Re: Sum With Similar Text Criteria

      use a formula something like this

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

      Comment


      • #4
        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

        Comment


        • #5
          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!

          Comment


          • #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, 20:34.

            Comment


            • #7
              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!
              .

              Comment


              • #8
                Re: Sum With Similar Text Criteria

                You can use Wildcards in SUMIF

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

                Comment


                • #9
                  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...

                  Comment


                  • #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

                    Comment


                    • #11
                      Re: Sum Range With Part Text In Another Range

                      it is working great!

                      Thank you very, very much!

                      Comment


                      • #12
                        Re: Sum Range With Part Text In Another Range

                        Hi,

                        A simple SUMIF should suffice.

                        =SUMIF(B1:B17,"*visa*finvaltr*",D1:D17)

                        HTH
                        Kris

                        ExcelFox

                        Comment


                        • #13
                          Re: Sum Range With Part Text In Another Range

                          That's what I thought, but got ignored

                          Comment


                          • #14
                            Re: Sum Range With Part Text In Another Range

                            Hi again!

                            I tried your formula Krishnakumar, it worked very well and it is simplier. Thank you a lot

                            P.S. Dave Hawley, I did try your formula as well, but I wanted the formula to find two words, and didn't know how to do that with the formula you wrote. Now I see the concept is the same with Krishnakumar's. Thank you anyway

                            Comment


                            • #15
                              Re: Sum Range With Part Text In Another Range

                              .....

                              Comment

                              Working...
                              X