Summing a crosstab
in [Functions]
|
Prev: don't want to have to change font in header and footer each time?
Next: Help needed with a formula for dates in excel 2007
From: dysolomon on 17 Mar 2010 10:35 That is pretty tricky! I like it. "Herbert Seidenberg" wrote: > Excel 2007 PivotTable > http://c0718892.cdn.cloudfiles.rackspacecloud.com/03_16_10a.xlsx > Pdf preview: > http://www.mediafire.com/file/j4qvm4jmmag/03_16_10a.pdf > > . >
From: Max on 17 Mar 2010 10:47 Just posted this, crossed with your reply ... A slight twist to the earlier. You could drop the DV in B10 and just use the year headers in B2:G2 instead to derive the entire lot at one go. In B11: =IF(ROWS($1:1)>COLUMNS($A:A),"",OFFSET($A$2,COLUMNS($A:A)+1-ROWS($1:1),ROWS($1:1))) Copy down to B16 In B17: =SUM(B11:B16) Select B11:B17, copy across to G17. B17:G17 gives the results. Joy? hit the YES below. Above and earlier formulas route works in any version of Excel. -- Max Singapore --- "dysolomon" wrote: > This is a great process and it worked great, but not exactly what I need. > > I would like to have all of the totals without having to choose the year. > That way, I could populate the results on a summary tab.
From: minyeh on 18 Mar 2010 04:30
On Mar 17, 10:47 pm, Max <demecha...(a)yahoo.com> wrote: > Just posted this, crossed with your reply ... > > A slight twist to the earlier. You could drop the DV in B10 and just use the > year headers in B2:G2 instead to derive the entire lot at one go. > In B11: > =IF(ROWS($1:1)>COLUMNS($A:A),"",OFFSET($A$2,COLUMNS($A:A)+1-ROWS($1:1),ROWS($1:1))) > Copy down to B16 > In B17: =SUM(B11:B16) > Select B11:B17, copy across to G17. B17:G17 gives the results. > Joy? hit the YES below. Above and earlier formulas route works in any > version of Excel. > -- > Max > Singapore > --- > > > > "dysolomon" wrote: > > This is a great process and it worked great, but not exactly what I need. > > > I would like to have all of the totals without having to choose the year. > > That way, I could populate the results on a summary tab.- Hide quoted text - > > - Show quoted text - Using array formula would be a great help: The data u provide is in A1:G7, my formula for year 2004 total is in cell B8 =SUMPRODUCT(--(ROW($B$2:$G$7)-ROW($B$2)+$B$1:$G$1=B$1),$B$2:$G$7) copy across for all the remaining years up to cell G8 it's easier to see in excel for the range used, note the $ sign for fixed cell and non-fixed cell. Hope this helps, straight forward use of sumproduct and less confusing for crosstab summing. Min Yeh |