View Full Version : Solved: Sumproduct using LEFT text from another worksheet?

Simon Lloyd

08-03-2010, 08:45 AM

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?

Bob Phillips

08-04-2010, 10:10 AM

As you wrote it it summed them all, but this should work

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

Simon Lloyd

08-04-2010, 11:00 AM

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?

Shred Dude

08-04-2010, 08:45 PM

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

Bob Phillips

08-05-2010, 12:21 AM

That should just be a simple SUMIF

=SUMIF(A1:A10,"*"&Sheet1!A1&"*",B1:B10)

Simon Lloyd

08-05-2010, 01:45 PM

@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?

Bob Phillips

08-05-2010, 03:02 PM

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)

Bob Phillips

08-05-2010, 03:06 PM

Unless of course you mean this

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

Simon Lloyd

08-05-2010, 04:13 PM

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.

Shred Dude

08-05-2010, 05:16 PM

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.

Simon Lloyd

08-05-2010, 07:02 PM

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.

Shred Dude

08-05-2010, 09:30 PM

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.

Shred Dude

08-05-2010, 09:44 PM

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

Simon Lloyd

08-05-2010, 10:35 PM

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"

Bob Phillips

08-06-2010, 01:03 AM

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

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.

Simon Lloyd

08-06-2010, 06:22 AM

That is because B1:B10 all had 1s, so I thought it was some weird way to use SUM to count :dunno

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

Shred Dude

08-06-2010, 08:04 AM

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

Simon Lloyd

08-06-2010, 09:03 AM

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

Shred Dude

08-06-2010, 09:26 AM

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.

Simon Lloyd

08-06-2010, 09:32 AM

If A1 is "Simon" i'd want to capture "Simon", "Simon ", "Simon Lloyd"....etc but not "SimonLloyd" :)

Bob Phillips

08-06-2010, 12:00 PM

One more try

=SUMPRODUCT(--(LEFT(Sheet1!$A$1:$A$10&" ",FIND(" ",A1&" "))=LEFT(A1&" ",FIND(" ",A1&" "))),Sheet1!$B$1:$B$10)

Simon Lloyd

08-06-2010, 12:27 PM

Yes!, yes!,yes!, thats it Bob. it counts "Simon". "Simon LLoyd" but not SimonLloyd

Brilliant, and such a short formula! :)

Shred Dude

08-06-2010, 09:54 PM

Nice!

I never thought to add a space. I was always trying to work with what was there. Great approach!

Shred

ajn946946

01-23-2011, 08:09 PM

Did not know you could do it that way.

:thumb

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.