The -- converts TRUE/FALSE to 1/0 which allows SUMPRODUCT to do the necessary
maths.

Look here for a more detailed explanation:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

"chrisk" wrote:

Oh my...it works...speechless. Thanks very much.
Yes, I can't add up that's why I use spread sheets!
Its warm and humid and I'm falling asleep.
IF(AND($A2=$A3,LEFT($B2,7)=LEFT($B3,7)),"", I get this bit

=SUMPRODUCT(--($A$2:$A$100=A2),--(LEFT($B$2:$B$100,7)=LEFT$B2,7)),$C$2:$C$100))
I don't get this bit. What's the '--' bit for?

"Toppers" wrote:



P.S. I think some of the totals in your previous respponse are wrong.


20/01/2007 0701/04/24 2
20/01/2007 0701/04/25 2
20/01/2007 0701/04/26 6
20/01/2007 0701/04/27 4
20/01/2007 0701/04/28 6 person 04 has done 16 ... 20??


"chrisk" wrote:

So close...but no biscuit...Sorry I'm brain dead.
I sort of get what the formula is doing, but that's it.

Here is part of the actual data I'm trying to sort

25/01/2007 0701/38/12 4
25/01/2007 0701/38/13 6
25/01/2007 0701/38/14 8 person 38 has done 18
27/01/2007 0701/15/22 8
27/01/2007 0701/15/23 6 person 15 has done 14
31/01/2007 0701/15/26 4
31/01/2007 0701/15/27 10 person 15 has done 14
10/01/2007 0701/50/18 10 person 50 has done 16
12/01/2007 0701/47/15 6
12/01/2007 0701/47/16 10 person 47 has done 16
11/01/2007 0701/04/06 8
11/01/2007 0701/04/07 4
11/01/2007 0701/04/08 4
11/01/2007 0701/04/09 3 person 04 has done 19
13/01/2007 0701/04/12 12
13/01/2007 0701/04/14 4 person 04 has done 16
17/01/2007 0701/04/20 16 person 04 has done 16
20/01/2007 0701/04/24 2
20/01/2007 0701/04/25 2
20/01/2007 0701/04/26 6
20/01/2007 0701/04/27 4
20/01/2007 0701/04/28 6 person 04 has done 16
24/01/2007 0701/04/33 4 and so on....
24/01/2007 0701/04/34 4
24/01/2007 0701/04/35 6
26/01/2007 0701/04/39 6
26/01/2007 0701/04/40 8
29/01/2007 0701/37/28 4
29/01/2007 0701/37/29 6
29/01/2007 0701/37/30 4
19/01/2007 0701/48/05 6
19/01/2007 0701/48/06 9
27/01/2007 0701/48/15 6
27/01/2007 0701/48/16 4
27/01/2007 0701/48/17 5
31/01/2007 0701/48/22 6
31/01/2007 0701/48/23 7
31/01/2007 0701/48/24 4


Thanks