Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 24

Thread: Solved: Sumproduct using LEFT text from another worksheet?

  1. #1
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location

    Solved: Sumproduct using LEFT text from another worksheet?

    Hi all, i'm having a little trouble getting this to show a value:
    =SUMPRODUCT(B1:B10)*(A1:A10=LEFT(Sheet1!A1,FIND(" ",Sheet1!A1)-1))

    On sheet1 A1 is constructed like this "Mon January" (without the quotes) in sheet 2 range A1:A10 i some cells just Mon and some with other text after it, however the SUMPRODUCT returns 0 but should return a sum of all those cells in column B that have Mon in the corresponding cell in column A.

    Any ideas?
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,275
    Location
    As you wrote it it summed them all, but this should work

    =SUMPRODUCT((B1:B10)*(A1:A10=LEFT(Sheet1!A1,FIND(" ",Sheet1!A1)-1)))
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Thanks Bob, simply missed ( & ) however i've made a bigger mistake than that, that formula will only sum the cells where "Mon" appears on its own, sumproduct still takes in to account the rest of the cell contents when summing, what i was trying to do is sum the range where the cell contains "Mon" & * (* being the rest of the contents) as the rest of the cell contents for this purpose are insignificant, is there a way to sum all the cells that begin with Mon regardless of the rest of the content?
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  4. #4
    Instead of using FIND, could you try SEARCH since it takes wildcards?

    Maybe something along the lines of:

    =SUMPRODUCT(B1:B10)*(-ISERR(SEARCH("MON*",sheet1!A1:A10))+1) or
    =SUMPRODUCT(B1:B10)*(-ISERR(SEARCH("MON*",sheet1!A1:A10))+1=1)

    I'm not sure I'm following what the A1:A10 have to do with the answer. The above should sum everything in B1:B10 where you have "MON" with or without any text thereafter at the beginning of cells A1:A10 on Sheet1.

    If I didn't interpret the requirement correctly, I'm hoping you can still adapt this use of coercing the result of SEARCH into a 1 or 0 to suit your needs.

    Regards,

    Shred

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,275
    Location
    That should just be a simple SUMIF

    =SUMIF(A1:A10,"*"&Sheet1!A1&"*",B1:B10)
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    @Shred Dude this =SUMPRODUCT(Sheet1!B1:B10)*(-ISERR(SEARCH("MON*",Sheet1!A1:A10))+1) just sums everything.

    @xld, this seems to only work if the only thing in A1 is "Mon" if there is anything else it returns 0, the other problem is if "Mon" appears anywhere other than first it's also summing that one.

    Any ideas?
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,275
    Location
    I am not sure why you say it should be 4 and not 7

    =SUMIF(Sheet1!A1:A10,"*"&LEFT(A1,FIND(" ",A1)-1)&"*",Sheet1!B1:B10)
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,275
    Location
    Unless of course you mean this

    =SUMPRODUCT(--(LEFT(Sheet1!A1:A10,FIND(" ",A1)-1)=LEFT(A1,FIND(" ",A1)-1)))
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Bob, i say it should be 4 because it should only sum this kind of cell value:
    Mon xxx or Mon but not xxx Mon

    Your last formula works (but isn't it just counting instances rather than summing B1:B10?) as long as there IS Mon xxx to look for, if just Mon appears in A1 the formula faults.

    EDIT: this sums the range =SUMPRODUCT((Sheet1!B1:B10)*--(LEFT(Sheet1!A1:A10,FIND(" ",A1)-1)=LEFT(A1,FIND(" ",A1)-1)))
    but still has the above problem where A1 must contain more than just "Mon" in order for the formula to work.
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  10. #10
    Does this get you there?

    =SUMPRODUCT((Sheet1!B1:B10)*--(LEFT(Sheet1!A1:A10,3)=LEFT(A1,3))*(OR(LEFT(A1,3)="mon",LEFT(A1,4)="mon ")))

    Gives me 4 with A1 having "mon", "mon ", "mon xxx". Gives 0 with "xxx mon", " mon", "abcd", etc.

  11. #11
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Yes that does it but it's not really Mon i'm looking for so can't hard code it, which is why i was using LEFT...etc, it needs to be the text before the space in A1 so it could read "Red Letter" so the formula should pick up RED as it appears before the first space.
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  12. #12
    If it's not a coincidence that Mon and Red are both 3 characters long, how about this? (Would the referenced text always begin with 3 characters before the space?)

    =IF(LEN(A1)>2,IF(AND(LEN(A1)>3,NOT(ISERR(FIND(" ",A1)))),SUMPRODUCT((Sheet1!B1:B10)*--(LEFT(Sheet1!A1:A10,FIND(" ",A1)-1)=LEFT(A1,FIND(" ",A1)-1))),SUMPRODUCT((Sheet1!B1:B10)*--(LEFT(Sheet1!A1:A10,3)=A1))),"")

    I didn't see a reference to the circumstance where A1 might not have a space in the 4th position, eg: "RedLetter", so i added the "Not(iserr..." bit in there.

    This formula seems to work based on my understanding of the requirement, that to be considered, A1 will contain at least 3 characters.

    You could ammend the final argument to address the situation where A1 contains less than 3 characters. As is you'll just see a blank.

    ___________

    If the requirement is that A1 could contain any length text and maybe have a space anywhere in there, and we only match on the text prior to the space if there is one, otherwise just on the first three characters... then how about this...?

    =IF(NOT(ISERR(FIND(" ",A1))), SUMPRODUCT((Sheet1!B1:B10)*--(LEFT(Sheet1!A1:A10,FIND(" ",A1)-1)=LEFT(A1,FIND(" ",A1)-1))),SUMPRODUCT((Sheet1!B1:B10)*--(LEFT(Sheet1!A1:A10,3)=A1)))


    Hope one of these, or a derivative of them can work for you....

    Shred

    PS: I'm assuming using a VBA UDF is out of the question? INSTR() seems like it could go along way on this one.

  13. #13
    So as to not hard code the "3" in the last example, you could substitute Len(a1), if that satisfied the requirement for the circumstance where there was no space in A1. Not sure what the real requirement is there.

    =IF(NOT(ISERR(FIND(" ",A1))), SUMPRODUCT((Sheet1!B1:B10)*--(LEFT(Sheet1!A1:A10,FIND(" ",A1)-1)=LEFT(A1,FIND(" ",A1)-1))),SUMPRODUCT((Sheet1!B1:B10)*--(LEFT(Sheet1!A1:A10,LEN(A1))=A1)))

  14. #14
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Thanks for sticking with this, it's proving to be tricky!
    This one =IF(NOT(ISERR(FIND(" ",A1))), SUMPRODUCT((Sheet1!B1:B10)*--(LEFT(Sheet1!A1:A10,FIND(" ",A1)-1)=LEFT(A1,FIND(" ",A1)-1))),SUMPRODUCT((Sheet1!B1:B10)*--(LEFT(Sheet1!A1:A10,LEN(A1))=A1))) works to some extent, however, the criteria will be any text before a space, so it could be 3 characters long or 30!

    The formula above will sum for Mon xxx just fine but unfortunately it also sums the Monday along with it, so if we were looking at the cell text "fiddle sticks" it should sum all occurrences of the first word but should not include "fiddlesticks" as its one word or to make it simpler has characters after the word we are looking for.

    In the example i used "Mon Jan"......etc just to give something to work with there can be many characters, if i was summing my name i would want to sum occurences of "Simon Lloyd", "Simon Jones", "Simon Walker" , "Simon" but would not want to sum "SimonLloyd", "SimonWhatever"
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  15. #15
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,275
    Location
    Quote Originally Posted by Simon Lloyd
    Your last formula works (but isn't it just counting instances rather than summing B1:B10?) as long as there IS Mon xxx to look for, if just Mon appears in A1 the formula faults.
    That is because B1:B10 all had 1s, so I thought it was some weird way to use SUM to count

    Quote Originally Posted by Simon Lloyd
    EDIT: this sums the range =SUMPRODUCT((Sheet1!B1:B10)*--(LEFT(Sheet1!A1:A10,FIND(" ",A1)-1)=LEFT(A1,FIND(" ",A1)-1)))
    but still has the above problem where A1 must contain more than just "Mon" in order for the formula to work.
    What do you mean? It works fine with Mon Tue, it adds that to Mon totals, although of course the proper way to write it is

    =SUMPRODUCT(--(LEFT(Sheet1!A1:A10,FIND(" ",A1)-1)=LEFT(A1,FIND(" ",A1)-1)),Sheet1!B1:B10)

    Give me an example of some text it does/does not include that is wrong.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  16. #16
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Quote Originally Posted by xld
    That is because B1:B10 all had 1s, so I thought it was some weird way to use SUM to count



    What do you mean? It works fine with Mon Tue, it adds that to Mon totals, although of course the proper way to write it is

    =SUMPRODUCT(--(LEFT(Sheet1!A1:A10,FIND(" ",A1)-1)=LEFT(A1,FIND(" ",A1)-1)),Sheet1!B1:B10)

    Give me an example of some text it does/does not include that is wrong.
    Thanks Bob,
    the formula provided will not sum if just "Mon" appears in A1 (show #Value) it does NOT sum "Tue Mon" "ThurMon" (which is correct), it DOES sum "Mon" (correct) "Mon Tue" (correct) "MonTue" (incorrect)

    So the only problems remaining are: if A1 contains just "Mon" the formula faults
    If there are instances where "Mon" appears with characters directly after it like "MonTue" then they are included.

    I suppose the check should be is there a space after "Mon" if not are there any characters after "Mon" if not then this is a valid value to sum along with the ones mentioned above which are correct..

    Sample attached
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  17. #17
    OK, I had another go over my morning coffee.

    Give this a try. I used your Simon etc. example from the previous post in the attached workbook. I think it does what you're looking for.



    =IF(NOT(ISERR(FIND(" ",A1))),SUMPRODUCT((Sheet1!B1:B10)*--(LEFT(Sheet1!A1:A10,FIND(" ",A1)-1)=LEFT(A1,FIND(" ",A1)-1))*(((LEN(Sheet1!A1:A10)=FIND(" ",A1)-1)+(MID(Sheet1!A1:A10,FIND(" ",A1),1)=" ")))),SUMPRODUCT((Sheet1!B1:B10)*--(LEFT(Sheet1!A1:A10,LEN(A1))=A1)))

  18. #18
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Shred thats closer, the only remaing problem with that one is if A1 only contains "Simon" it will sum all instances where Simon is the first word, so it will sum SimonLloyd....etc
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  19. #19
    So, in the case where A1 does not contain a space, eg: "Simon", what would you want the answer to be? Just the instances where there is an exact match?

    If so, change the last element to this:

    =IF(NOT(ISERR(FIND(" ",A1))),SUMPRODUCT((Sheet1!B1:B10)*--(LEFT(Sheet1!A1:A10,FIND(" ",A1)-1)=LEFT(A1,FIND(" ",A1)-1))*(((LEN(Sheet1!A1:A10)=FIND(" ",A1)-1)+(MID(Sheet1!A1:A10,FIND(" ",A1),1)=" ")))),SUMPRODUCT((Sheet1!B1:B10)*--(Sheet1!A1:A10=A1)))

    Or, when a1="Simon ", would you want to capture "Simon" and "Simon ", but not "Simon Lloyd", then you could do this:

    =IF(NOT(ISERR(FIND(" ",TRIM(A1)))),SUMPRODUCT((Sheet1!B1:B10)*--(LEFT(Sheet1!A1:A10,FIND(" ",A1)-1)=LEFT(A1,FIND(" ",A1)-1))*(((LEN(Sheet1!A1:A10)=FIND(" ",A1)-1)+(MID(Sheet1!A1:A10,FIND(" ",A1),1)=" ")))),SUMPRODUCT((Sheet1!B1:B10)*--(TRIM(Sheet1!A1:A10)=TRIM(A1))))

    does that get it? Not sure what you want the answer to be if just "Simon" was in A1.

  20. #20
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    If A1 is "Simon" i'd want to capture "Simon", "Simon ", "Simon Lloyd"....etc but not "SimonLloyd"
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

Posting Permissions

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