Sumproduct using LEFT text from another worksheet?

[Simon Lloyd]

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]

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]

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]

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]

That should just be a simple SUMIF

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

[Simon Lloyd]

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

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]

Unless of course you mean this

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

[Simon Lloyd]

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]

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]

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]

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]

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]

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]

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

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]

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

[Shred Dude]

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]

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]

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]

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