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 16 Mar 2010 17:18 Is there a way to do a sumif on a crosstab? For example: Issue Year Duration 2,004 2,005 2,006 2,007 2,008 2,009 0 453 - - - - 505 1 - - 789 498 - 2 650 - - - 3 5,496 1,086 2,210 4 - 6,241 5 13,664 I want to summarize by Incurred Year, which would be Issue year + Duration So for Incurred Year 2009, I want to sum all cells where the Issue Year + Duration = 2009. sumif (row + col = year) Incurred Year Amount 2009 22,621 2008 1,584 2007 6,285 2006 650 2005 - 2004 453 I don't know whether to use sumif, match, lookup, or if it is even possible. Thanks.
From: Max on 16 Mar 2010 22:41 Here's one relatively simple play which delivers it .. Assume your source table as posted is in A1:G8 where in B2:G2 are the years: 2004, 2005 ... 2009 and in A3:A8 are the durations: 0, 1,... 5 Create a DV in say, cell B10 to select the year (from B2:G2) [Click Data > Validation, Allow: List, Source: =$B$2:$G$2] Then place this in B11: =IF(ROWS($1:1)>MATCH($B$10,$B$2:$G$2,0),"",OFFSET($A$2,MATCH($B$10,$B$2:$G$2,0)+1-ROWS($1:1),ROWS($1:1))) Copy B11 down to B16 (As the source is a 6 x 6 table, hence copy down 6 cells to cover the max results range). This is the key formula which strips the diagonal cells from the source table depending on the year selected in B10 Then in B17: =SUM(B11:B16) will yield the desired result Inspiring? hit the YES below -- Max Singapore --- "dysolomon" wrote: > Is there a way to do a sumif on a crosstab? > > For example: > > Issue Year > Duration 2,004 2,005 2,006 2,007 2,008 2,009 > 0 453 - - - - 505 > 1 - - 789 498 - > 2 650 - - - > 3 5,496 1,086 2,210 > 4 - 6,241 > 5 13,664 > > I want to summarize by Incurred Year, which would be Issue year + Duration > > So for Incurred Year 2009, I want to sum all cells where the Issue Year + > Duration = 2009. sumif (row + col = year) > > Incurred Year Amount > 2009 22,621 > 2008 1,584 > 2007 6,285 > 2006 650 > 2005 - > 2004 453 > > > I don't know whether to use sumif, match, lookup, or if it is even possible. > > Thanks. >
From: Herbert Seidenberg on 16 Mar 2010 23:16 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: dysolomon on 17 Mar 2010 10:29 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. "Max" wrote: > Here's one relatively simple play which delivers it .. > > Assume your source table as posted is in A1:G8 > where in B2:G2 are the years: 2004, 2005 ... 2009 > and in A3:A8 are the durations: 0, 1,... 5 > > Create a DV in say, cell B10 to select the year (from B2:G2) > [Click Data > Validation, Allow: List, Source: =$B$2:$G$2] > Then place this in B11: > =IF(ROWS($1:1)>MATCH($B$10,$B$2:$G$2,0),"",OFFSET($A$2,MATCH($B$10,$B$2:$G$2,0)+1-ROWS($1:1),ROWS($1:1))) > Copy B11 down to B16 (As the source is a 6 x 6 table, hence copy down 6 > cells to cover the max results range). This is the key formula which strips > the diagonal cells from the source table depending on the year selected in > B10 > Then in B17: =SUM(B11:B16) will yield the desired result > Inspiring? hit the YES below > -- > Max > Singapore > --- > "dysolomon" wrote: > > Is there a way to do a sumif on a crosstab? > > > > For example: > > > > Issue Year > > Duration 2,004 2,005 2,006 2,007 2,008 2,009 > > 0 453 - - - - 505 > > 1 - - 789 498 - > > 2 650 - - - > > 3 5,496 1,086 2,210 > > 4 - 6,241 > > 5 13,664 > > > > I want to summarize by Incurred Year, which would be Issue year + Duration > > > > So for Incurred Year 2009, I want to sum all cells where the Issue Year + > > Duration = 2009. sumif (row + col = year) > > > > Incurred Year Amount > > 2009 22,621 > > 2008 1,584 > > 2007 6,285 > > 2006 650 > > 2005 - > > 2004 453 > > > > > > I don't know whether to use sumif, match, lookup, or if it is even possible. > > > > Thanks. > >
From: Max on 17 Mar 2010 10:32
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 --- |