Multiple variables to sort and sum, return values<0 with sum refer

[Stinky]

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!!!

 

[Steve Dunn]

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

in F2 (array formula*):

=INDEX($A$2:$A$14&"-"&$D$2:$D$14,MATCH(0,COUNTIF(F$1:$F1,$A$2:$A$14&"-"&$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]

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]

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

 

[Stinky]

In case you get reply notification, Steve, I just thought I'd let you know
that I conquered the PivotTables and it has completely revolutionised my
life!! I never would have known about it if you hadn't mentioned it - I
only wish I'd tried this forum 100 years ago (well, that's how long it seems
I've been laboriously working with ridiculous amounts of data for
)..........I've now shown other colleagues how brilliant they are for what we
do and it's been declared the 'discovery of the year'.

Taking the time to respond to these threads is SO valuable. Thanks a million.

 

[Steve Dunn]

Thanks for the feedback, it's always good to hear a happy ending...