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


S

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

Advertisements

S

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 A1:D14,

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.
 
S

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

Steve Dunn

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

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.
 
Ad

Advertisements

Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top