You're welcome (and PivotTables are nowhere near as scary as they may at
first seem).


"Stinky" wrote in message
...
Wow! Thanks Steve it worked - excellent, it's going to save my team huge
amount of time. I'll take your advice and learn PivotTables next!

"Steve Dunn" wrote:

The easiest way to do this would be with a PivotTable, but you could use
the
following formualae, assuming that your data is in A1:D14,

in F2 (array formula*):

=INDEX($A$2:$A$14&"-"&$D$2:$D$14,MATCH(0,COUNTIF(F$1:$F1,$A$2:$A$1 4&"-"&$D$2:$D$14),0))

in G2:

=LEFT($F2,FIND("-",$F2)-1)

in H2:

=TEXT(VALUE(RIGHT($F2,5)),"dd/mm/yyyy")

in I2:

=SUMPRODUCT(($A$2:$A$14=$G2)*($D$2:$D$14=VALUE($H2 ))*$C$2:$C$14)

copy F2:I2 down to F14:I14, and hide column F.


*to enter an array formula press Ctrl+Shift+Enter instead of just Enter.






"Stinky" wrote in message
...
Can anyone help me with this one please? I have this table of data.
I'd
like
to be able to write a formula(s) which sums the No according to date
and
code, but then only returns a sum value (with the code and date in the
two
adjacent cells) if there is a value greater than 0.

Code Ref No Date
1 G/032/05/999 400600 212 19/03/2010
2 G/032/03/001/999 400500 50 19/03/2010
3 G/032/02/001/001 400400 170 19/03/2010
4 G/032/05/999 400600 315 19/03/2010
5 G/032/03/001/999 400500 300 19/03/2010
6 G/032/05/999 400600 202 19/03/2010
7 G/032/03/001/002 400500 85 19/03/2010
8 G/032/05/999 400600 238 19/03/2010
9 G/032/03/001/002 400500 170 19/03/2010
10 G/032/02/001/001 400400 170 20/03/2010
11 G/032/05/999 400600 53 20/03/2010
12 G/032/03/001/999 400500 430 21/03/2010
13 G/032/05/999 400600 371 21/03/2010

The result of formula(s) would look something like:

G/032/05/999 19/03/2010 967
G/032/03/001/999 19/03/2010 350
G/032/02/001/001 19/03/2010 170 and so on.....

I think I'm wishing for the moon, but would solve a major headache in
my
daily working life!!!