Data amalgamation

  • Thread starter Thread starter Richard Glover
  • Start date Start date
R

Richard Glover

I have a table of data, including columns for date, a code indicatin
data source, and various analysis under headings.
As follows:
Date Source A B C
1/1/01 X 2 4 8
15/3/01 X 3 5 7
16/6/02 Y 12 1 9

I want a summary table that is driven by a (variable) date range
amalgamating data by source and A,B,C.
As follows:
X Y Z
Pre 30/6/01
- A 5 0 0
- B 9 0 0
- C 15 0 0
Post 30/6/01
- A 12 0 0
- B 0 1 0
- C 0 9 0

I could work it with sumif. However the middle argument needs a har
coded number (the date range), but I want that argument to use
variable sourced from another cell.

Any pointers would be gratefully received.
Thanks :
 
With your data in cells A1:E4, and the date in cell H1, enter your
source headings (X,Y,Z) in cell B6:D6. In cell A7, enter the formula:

="Pre " & TEXT($H$1,"dd/mm/yy")

In cells A8:A10, enter your Analysis headings (A,B,C)
In cell B8, enter:
=SUMPRODUCT(($C$1:$E$1=$A8)*($B$2:$B$4=B$6)*($A$2:$A$4<$H$1)*($C$2:$E$4))
Copy across to D8, and down to D10

In cell A13: ="Post " & TEXT($H$1,"dd/mm/yy")
In cells A14:A16, enter your Analysis headings (A,B,C)
In cell B14, enter:
=SUMPRODUCT(($C$1:$E$1=$A14)*($B$2:$B$4=B$6)*($A$2:$A$4>$H$1)*($C$2:$E$4))
Copy across to D14, and down to D16

Both formulas exclude the date in H1. You could change either formula to
include it, e.g. ($A$2:$A$4<=$H$1) or ($A$2:$A$4>=$H$1)
 
Back
Top