| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple variables to sort and sum, return values<0 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!!! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple variables to sort and sum, return values<0 with sum refer
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!!! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple variables to sort and sum, return values<0 with sum r
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!!! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple variables to sort and sum, return values<0 with sum r
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!!! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple variables to sort and sum, return values<0 with sum r
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 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!!! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple variables to sort and sum, return values<0 with sum r
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count w/ multiple variables & text values | Excel Worksheet Functions | |||
| Refer to adjacent cells as variables in formula | Excel Discussion (Misc queries) | |||
| Find a Match in Multiple Places & Return Multiple Values | Excel Worksheet Functions | |||
| Lookup in Multiple Columns, Return Multiple Values | Excel Worksheet Functions | |||
| Search multiple values to return single values | Excel Worksheet Functions | |||
Linear Mode
