Multiple variables to sort and sum, return values0 with sum refer

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

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

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 A114,

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

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 A114,

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

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" wrote:

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 A114,

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

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


"Stinky" wrote in message
...
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.