Printable View
Hi all, i'm having a little trouble getting this to show a value:
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.Code:=SUMPRODUCT(B1:B10)*(A1:A10=LEFT(Sheet1!A1,FIND(" ",Sheet1!A1)-1))
Any ideas?
As you wrote it it summed them all, but this should work
Code:=SUMPRODUCT((B1:B10)*(A1:A10=LEFT(Sheet1!A1,FIND(" ",Sheet1!A1)-1)))
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?
Instead of using FIND, could you try SEARCH since it takes wildcards?
Maybe something along the lines of:
)Code:=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
That should just be a simple SUMIF
Code:=SUMIF(A1:A10,"*"&Sheet1!A1&"*",B1:B10)
@Shred Dude thisjust sums everything.Code:=SUMPRODUCT(Sheet1!B1:B10)*(-ISERR(SEARCH("MON*",Sheet1!A1:A10))+1)
@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?
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)
Unless of course you mean this
Code:=SUMPRODUCT(--(LEFT(Sheet1!A1:A10,FIND(" ",A1)-1)=LEFT(A1,FIND(" ",A1)-1)))
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 rangebut still has the above problem where A1 must contain more than just "Mon" in order for the formula to work.Code:=SUMPRODUCT((Sheet1!B1:B10)*--(LEFT(Sheet1!A1:A10,FIND(" ",A1)-1)=LEFT(A1,FIND(" ",A1)-1)))
Does this get you there?
Gives me 4 with A1 having "mon", "mon ", "mon xxx". Gives 0 with "xxx mon", " mon", "abcd", etc.Code:=SUMPRODUCT((Sheet1!B1:B10)*--(LEFT(Sheet1!A1:A10,3)=LEFT(A1,3))*(OR(LEFT(A1,3)="mon",LEFT(A1,4)="mon ")))
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.
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?)
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.Code:=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))),"")
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...?
Code:=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.
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.
Code:=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)))
Thanks for sticking with this, it's proving to be tricky!
This one
works to some extent, however, the criteria will be any text before a space, so it could be 3 characters long or 30!Code:=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)))
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"
That is because B1:B10 all had 1s, so I thought it was some weird way to use SUM to count :dunnoQuote:
Originally Posted by Simon Lloyd
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 isQuote:
Originally Posted by Simon Lloyd
Give me an example of some text it does/does not include that is wrong.Code:=SUMPRODUCT(--(LEFT(Sheet1!A1:A10,FIND(" ",A1)-1)=LEFT(A1,FIND(" ",A1)-1)),Sheet1!B1:B10)
Thanks Bob,Quote:
Originally Posted by xld
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 :)
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.
Code:=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)))
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
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:
Or, when a1="Simon ", would you want to capture "Simon" and "Simon ", but not "Simon Lloyd", then you could do this:Code:=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)))
does that get it? Not sure what you want the answer to be if just "Simon" was in A1.Code:=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))))
If A1 is "Simon" i'd want to capture "Simon", "Simon ", "Simon Lloyd"....etc but not "SimonLloyd" :)
One more try
Code:=SUMPRODUCT(--(LEFT(Sheet1!$A$1:$A$10&" ",FIND(" ",A1&" "))=LEFT(A1&" ",FIND(" ",A1&" "))),Sheet1!$B$1:$B$10)
Yes!, yes!,yes!, thats it Bob. it counts "Simon". "Simon LLoyd" but not SimonLloyd
Brilliant, and such a short formula! :)
Nice!
I never thought to add a space. I was always trying to work with what was there. Great approach!
Shred
Did not know you could do it that way.
:thumb