Prev: Calculation of weighed average
Next: Include CDF to workbook
From: Stinky on 9 Apr 2010 05:11
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!!!
From: Steve Dunn on 9 Apr 2010 07:15
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.






"Stinky" <Stinky(a)discussions.microsoft.com> wrote in message
news:AB6FB309-4A74-4FDA-804B-71022CFF336D(a)microsoft.com...
> 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!!!

 | 
Pages: 1
Prev: Calculation of weighed average
Next: Include CDF to workbook