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.