Summing a crosstab

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.

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.

Excel 2007 PivotTable
http://c0718892.cdn.cloudfiles.racks...03_16_10a.xlsx
Pdf preview:
http://www.mediafire.com/file/j4qvm4jmmag/03_16_10a.pdf

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.

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,COLUMN S($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
---

That is pretty tricky! I like it.

"Herbert Seidenberg" wrote:

Excel 2007 PivotTable
http://c0718892.cdn.cloudfiles.racks...03_16_10a.xlsx
Pdf preview:
http://www.mediafire.com/file/j4qvm4jmmag/03_16_10a.pdf

.

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,COLUMN S($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.

On Mar 17, 10:47*pm, Max 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,COLUMN S($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