Excel 2007 : How to calculate count of certain column values in a pivot table

Hi,


For a column S, I have 4 set of values - S1, S2, S3 and S4. One more column T having values T1,T2 and T3 for which column S is used to indicate status. Pivot table can be drawn with T and S. With T in the row and S in the row and column.

T S1 S2 S3 S4
T1 4 4 5 6
T2 3 3 3 5
T3 2 1 4 5

My question is to have one more column S5 = S1 + S2 + S3

Resultant :

T S1 S2 S3 S4 S5
T1 4 4 5 6 13
T2 3 3 3 5 9
T3 2 1 4 5 7

How this can be done with pivot table? Any ideas are highly appreciated.

You need to create a calculated Item (not Field). You have to select one of the S1-S4 cells in the table before you will be able to add a calculated item.

I know that - that is what I was answering.

I do not have 2007 available to test, so will have to do this from memory.
With your pivot table created, select one of the S1, S2, S3 or S4 header cells, then on the Pivot Table tools tab, look for a button called something like 'Formulas'. Click this, and choose Calculated Item. Enter a name for the item in the dialog (say S5) then in the formula box enter S1+S2+S3 and choose Add.

I have however just remembered one restriction - you cannot create a calculated item in a pivot field which you also use in the data area - is there another field that you can use for the data besides the T and S fields? If not, you will need a formula in the underlying data table instead.

Do you have a text field you can use for the data? If so, there should be no need to use custom subtotals.

Edit: attached a sample pivot.

You would have to recreate the totals with another calculated item using S5+S4. There is no way to get the built-in totals to ignore certain items.

My pleasure.

Please do not forget to mark the thread solved.

It depends really on how best you learn. I have learned what little I know from books and experimenting with the program, but you may prefer the structure of classes. Either way, practice is essential to remember it.