removing "sum of"/"count of" from Pivot Table headings

[Caravishah]

Hi,

Simply put my question is how do we get rid of the "sum of"/"count of" etc. that comes along when we run pivot on a data table? this might be simple but i tried googling around but didn't find a solution.

I want to vlookup on a pivot table (date as column) but the issue is pivot has "sum of (month) Jan-14" and my table directly has the month i.e Jan-14 in date format.

The helper cell using right to extract Jan-14 doesn't help because the extracted Jan-14 is not in date format.

Any thoughts/ideas? Thanks.

 

[Chihiro]

Click on the value field and go to value field settings.

Type the name you want in there.

 

[Caravishah]

Thanks Chihiro. However there are 2 issues with the solution.

1. I have about 108 coloumn and cant do it manually for each and every coloumn.

2. I tried doing for a couple of them and I don't know why but it said "Pivot Table field name already exists."

Any thoughts/ideas? Thanks.

 

[Chihiro]

1. Why so many? I'd recommend using helper columns (to group columns) or grouping in pivot.

2. If the name already exists in the source (or in pivot), it will not allow you to change it to the same name.

Also, using Pivot table to do calculation else where may not be ideal. As slight change in structure will throw off your calculation. It may be better to copy pivoted data as value only elsewhere to do your calculations.

 

[Caravishah]

I'm re-posting the question with more info for other users to answer -

Hi,

Simply put my question is how do we get rid of the "sum of"/"count of" etc. that comes along when we run pivot on a data table? I want it to remain same as original data and not add words "Sum of" before the original field name. This might be simple but i tried googling around but didn't find a solution.

I want to vlookup on a pivot table (date as column) but the issue is pivot has "sum of (month) Jan-14" and my table directly has the month i.e Jan-14 in date format.

The helper cell using right function to extract Jan-14 from "Sum of Jan-14" in the pivot doesn't help because the extracted Jan-14 is not in date format.

CONCATENATE to make vlookup key ("sum of "&ref to date cell) also doesn't help because it picks up date as number. So it doesn't match with "Sum of Jan-14" (general format) in the pivot table.

Any thoughts/ideas? Thanks.

 

[Deepak]

You may convert date to text! like as below...

=MATCH("Sum of "&TEXT(B11,"MMM-YY"),$H$2:$J$2,0)

 

[Deepak]

This might be useful too..

=MATCH(B11,INDEX(--SUBSTITUTE(SUBSTITUTE($H$2:$J$2,"Sum of ",""),"Count of ",""),,),0)

 

[Caravishah]

Deepak that is exactly what I was looking out for. Thanks so much.

Would be grateful if you can let me know how your substitute formula works. esp. the "--" before the 1st substitute. What are they for?

 

[NARAYANK991]

Hi ,

I do not know what you are trying to do , but the initial post you made mentioned that using the RIGHT function to extract the month does not convert it to date format.

This is correct because the RIGHT function is a string / text function , and it is not supposed to convert a text value into a numeric value.

All you have to do in this case , to convert a text value you get by using the RIGHT function to a numeric value , is to add 0 , as follows :

=RIGHT(H2,6) + 0

With this change , you can go back to using the VLOOKUP function , in case this was the reason you could not use it.

Narayan