Pivot table formulas

Hi,

I am trying to create a calculated field in a pivot table but cannot get the
expression to work correctly.

I have a field called Class Description and I need to perform a COUNT
function on it. I have entered =COUNT('''Class Description''') as the
formula but Excel always returns 1.

Does anyone have any ideas how I can resolve this please?



0
10/1/2004 11:30:16 AM
excel 39879 articles. 2 followers. Follow

3 Replies
448 Views

Similar Articles

[PageSpeed] 41

Hi Alan

if its a text field then excel will automatically "count" it when you drag 
it into the data area - or am i completely misunderstanding what you're 
after.

Cheers
JulieD

"Alan Bentley" <alanhbentley@hotmail.com> wrote in message 
news:eMpTXm6pEHA.3688@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> I am trying to create a calculated field in a pivot table but cannot get 
> the
> expression to work correctly.
>
> I have a field called Class Description and I need to perform a COUNT
> function on it. I have entered =COUNT('''Class Description''') as the
> formula but Excel always returns 1.
>
> Does anyone have any ideas how I can resolve this please?
>
>
> 


0
JulieD1 (2295)
10/1/2004 2:08:38 PM
Thanks Julie,

I understand what you say but my question is a little deeper...

I need to be able to multiply a Pivot table field by the number of items
that have been consolidated.  E.g. I have different items each with its own
class description and I have a column in the PT that performs a count of
those so I can see how many there are.  But I cannot find a way to use this
value in a formula to multiply another column.  I thought that = '''Cost'''
* COUNT('''Class Description''') would do it but that formula resolves
COUNT('''Class Description''') to be always 1.

Any ideas?


"JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
news:uhLk3A8pEHA.2696@TK2MSFTNGP15.phx.gbl...
> Hi Alan
>
> if its a text field then excel will automatically "count" it when you drag
> it into the data area - or am i completely misunderstanding what you're
> after.
>
> Cheers
> JulieD
>
> "Alan Bentley" <alanhbentley@hotmail.com> wrote in message
> news:eMpTXm6pEHA.3688@TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I am trying to create a calculated field in a pivot table but cannot get
> > the
> > expression to work correctly.
> >
> > I have a field called Class Description and I need to perform a COUNT
> > function on it. I have entered =COUNT('''Class Description''') as the
> > formula but Excel always returns 1.
> >
> > Does anyone have any ideas how I can resolve this please?
> >
> >
> >
>
>


0
10/1/2004 2:43:44 PM
It's not clear to me exactly what you're trying to do, but you could add 
a column to the database, then add that field to the pivottable.

For example, if you want to count the customers in column A, use the
following formula in row 2:
   =IF(COUNTIF(A$2:A2,A2)=1,1,0)

Copy this formula down to all rows in the database.

Or, to count all customer records, enter a 1 in each record in the column.

Add this field to the pivot table data area, as a Sum, and you'll get a
count of unique items, or a count of records. You could multiply this 
field in your formula.


Alan Bentley wrote:
> Thanks Julie,
> 
> I understand what you say but my question is a little deeper...
> 
> I need to be able to multiply a Pivot table field by the number of items
> that have been consolidated.  E.g. I have different items each with its own
> class description and I have a column in the PT that performs a count of
> those so I can see how many there are.  But I cannot find a way to use this
> value in a formula to multiply another column.  I thought that = '''Cost'''
> * COUNT('''Class Description''') would do it but that formula resolves
> COUNT('''Class Description''') to be always 1.
> 
> Any ideas?
> 
> 
> "JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
> news:uhLk3A8pEHA.2696@TK2MSFTNGP15.phx.gbl...
> 
>>Hi Alan
>>
>>if its a text field then excel will automatically "count" it when you drag
>>it into the data area - or am i completely misunderstanding what you're
>>after.
>>
>>Cheers
>>JulieD
>>
>>"Alan Bentley" <alanhbentley@hotmail.com> wrote in message
>>news:eMpTXm6pEHA.3688@TK2MSFTNGP09.phx.gbl...
>>
>>>Hi,
>>>
>>>I am trying to create a calculated field in a pivot table but cannot get
>>>the
>>>expression to work correctly.
>>>
>>>I have a field called Class Description and I need to perform a COUNT
>>>function on it. I have entered =COUNT('''Class Description''') as the
>>>formula but Excel always returns 1.
>>>
>>>Does anyone have any ideas how I can resolve this please?
>>>
>>>
>>>
>>
>>
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
10/1/2004 8:54:48 PM
Reply:

Similar Artilces:

error in formula #2
Using Excel 2007 and working on a xls file I am trying to count the occurrences of a certain value (in this case "1940 Census") in a column. I tried to follow the example in the XL Help and used this formula: =SUM(IF((L2:L303="1940 Census"),1,0)) but the result I get is #VALUE! and the error info says "a value used in the formula is the wrong type" The column L2-L303 contains either blank cells, or "1940 Census", or just words like "immigration", "History", etc. Can someone suggest how I should correct this formula? I also wan...

Date Formula #3
Is there a simple way to lookup a value using the day of the week a the criterion? From a list of dates, I want to see if there is statistical correlation if a certain event happens every, say, Tuesda rather than another day of the week. Something like this: A Mon. 5/17 Tues. 5/18 Wed. 5/19 (etc.) I want to lookup all values in this range that fall on Tuesday. tried AutoFilter/Custom/Contains but that didn't work. Can this b done? Thanks -- Message posted from http://www.ExcelForum.com Hi Looking at your dates I see they are text and not True dates. In this case try AutoFil...

Another Formula Need
Thanks for all every ones help. But I need another formula. I changed the format of the spreadsheet. The columns are as follows: Date, Description, Hotel, Transport, Mileage, Mileage Cost, Meals, Phone, Entertainment, Misc, and Total. I need a formula in the total field that would automatically calculate the mileage cost (e.g.miles time 0.405) with all the other fields. I do not want the actual mileage rate of 0.405 to show on the spreadsheet. I just want the mileage cost to appear in that field. Lonz, I am making the assumption that all the heading you have specified are columns. ...

Variables in URL formula help
Can anyone help me solve a simple but annoying problem? I have a formula that links to a URL ='http://www.domain.com/18 Apr 2007/[results.xls]results'!$D$2 which gives back the value 3 (as it should) Now I want to draw 18 Apr 2007 from a cell value found in an open file called reference.xls. So I tried ="'http://www.domain.com/" &'[reference.xls]sheet1'!$A$53 &"/[results.xls]results'!$D$2" but it just shows the words 'http://www.domain.com/18 Apr 2007/[results.xls]results'!$D$2 instead of the value Can anyone tell me the rig...

Formula for mm/dd/yyyy extract into separate columns?
How do I extract mm/dd/yyyy into separate columns Sneilan Sneilan, use the Text to Columns command on the Data menu. Delimited text, with / as the separator. Afterwards, format all cells as General. DDM "DDM's Microsoft Office Tips and Tricks" www.ddmcomputing.com "Sneilan" <anonymous@discussions.microsoft.com> wrote in message news:164CB494-168D-408E-A3F6-2643521642A7@microsoft.com... > How do I extract mm/dd/yyyy into separate columns? > > Sneilan Sneilan, If A1 = mm/dd/yyyy, then use the following formulas in other cells B1 = MONTH(A1) C1 = D...

Formula to give special date format but with addition?
To get this format of date in cell B1, with my abbreviated days: 071117.Sa I was given the cell formula of this: =IF(A2<>"",TEXT(A2,"yymmdd.")&CHOOSE(WEEKDAY(A2),"Sn","Mn","Tu","Wd","Th","Fr","Sa"),"") It's straightforward because it just copies the date from A2 into the correct date format for the purpose needed in this particular sheet. However, I have some addition in another cell where I'd like to slightly change the date format to our particular format to standardize the ...

update a table based on a sum query
Hi All, I have seen the zillions of answers to this that state "never add a total into the table.." But there are cases, such as mine when it is prefered. I want to update a project table with the final invoice cost. I want it entered as a hard figure so that I can then export the table for accounting purposes. Additionally, once an invoice has been generated, there isn't any need for the total to ever, ever change! In fact it shouldn't. So the question is.... is there a way to do this? To update a table with a value from a sum query? Thanks for any help. ...

Comparing data between two tables
Dear all, In my access database there are two tables. The first one has complete information: customer_code, item_code, item_code_2, company, and values. The second has only the item_code, item_code_2 and values. Now I need to compare the values between the tables, but I have a big problem: I need the all data in the first table, and when I execute the consult the values stay in duplicity. I already think in create another table, but I don't know... because I have some data in one table that I don't have in another... How can I solve this problem? Thanks a lot!!! Andr=E9. On Oct...

Help with Formula in Excel 2003
I have a column of data which I add to twice a day for one month. I have a second column which calculates the daily average. I need to calculate the difference between the last entry in the average column & the first, placing the result into another cell. What I can't find is how to make this a dynamic update. Any ideas please? =AVERAGE(H2:OFFSET(H2,MATCH(9999,H:H),0)) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Andy" <Andy@discussions.microsoft.com> wrote in message news:418C8345-4A2E-42F2-8A0E-43FF1BC2BD13@microso...

tableadapter Query linked to Access 2003 table
I'm trying to write a query for my Dataset's table adapter. It is linked to an access 2003 database. Basically, I'm trying to duplicate the following query. SELECT Parts_Inventory.Aisle, Parts_Inventory.Rack, Parts_Inventory.Shelf, Parts_Inventory.QNTY, Parts_Inventory.MFG_PN, Parts_Inventory.Manufacturer, Parts_Inventory.Description FROM Parts_Inventory WHERE (((Parts_Inventory.Manufacturer) Like "*" & [?] & "*")); I'm using a text box as my control to the manufacturer field. If it is blank, it will show all my parts. I'm able...

Pivot ungrouping on refresh?
Hi, I have a pivot table where the ROW field is a date that is grouped by month & year. However, when I change the data source to include additional rows that I have added to the underlying data & refresh the pivot, the grouping is ungrouped, so my aggregagation by month becomes aggregation by date. this is a huge problem as it adds hundreds of rows to my pivot table which then runs over another pivot table. Does anyone know how I can retain the row grouping on updating the data source & refreshing. Thanks, Jason Hi Jason You should be using a ...

League Table follow up
Hi Although this is posted as a new thread it is really a supplementary question a reply fro0m Mike H. I show again an extract from my worksheet and the formula to get the draws from the fixtures. =SUMPRODUCT(($A$1:A$6=E1)*($C$1:$C$6=$D$1:$D$6))+SUMPRODUCT(($B$1:$B$6=E1)*($D$1:$D$6=$C$1:$C$6)) My question is that this formula works fine for games that have already been played & results entered in Columns C&D, however also assumes that unplayed games where no results have been entered are also draws and so can you suggest a simple amendment to the formula to disregar...

Formulas using dates, pls help!
Hi all, A newbie here. I'm putting together a spreadsheet to track variou budgets. I am stumped. What I want to do is... I have two columns of data - projected budget and actual budget. (ie am tracking actual funds committed vs actually invoiced). I have thes in two columns. Under the 'invoiced?' column I have the cells with th default "N", which I will change to "Y" when an invoice is received an paid. I have used SUMIF to sum the 'Invoiced?' column total as a "N" i turned into a "Y". Well heres the question. I then want a thi...

One More Formula Question
I need to get a specific total from the following criteria. I have data in 2 columns and need to get a total based upon specific entries in the cells in thoses columns. For example: Column A has 4 choices from a picklist. (N,P,C,R) Column B has a different set of choices. (ENG,OP,T&E,RS) What I need to do is be able to get an answer for how many N's are also ENG's Thanks -- doc ------------------------------------------------------------------------ doc's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1434 View this thread: http://www.excelforu...

Delete two columns with formula
I need to delete 2 columns (with formula) and only display the last two columns. When I delete the first 2 columns, the last two columns display #REF!. I know I can copy the info I need to a new sheet and paste special the value. Is there a way to fix this on the current sheet I am working on. Thanks. "ED" <ED@discussions.microsoft.com> wrote in message news:1944C248-624E-4D95-AD8E-86412991A77F@microsoft.com... >I need to delete 2 columns (with formula) and only display the last two > columns. > > When I delete the first 2 columns, the last two columns di...

Can you write protect a table in a database?
Hi, I wonder if it's possible to make a table (for all users) write protected (I don't want people by accident changing data)? Cheers, Mikael Sweden Well, that's one way to get around the problem. But I really want to know if it's possible to write-protect the table, and if so, how? Cheers, Mikael Well, that's one solution to the problem. But I would really like to know if it's possible to write-protect a table, and if so, how? Cheers, Mikael If you are using Access 2003 or earlier you can use User Level Security to have the table 'read only' for c...

Football League Table Spreadsheet
Hi, Forgive me if I have posted in the wrong section but it is my first post. I am looking to use a spreadsheet to contain a league table and fixture list for a church league which has ten teams playing each other twice in a season. I want to be able to just add the scores in to the fixture list and have the table update itself accordingly. I want the table to have columns for the following: Played/Won/Lost/Drawn/Goals For/Goals Against/Goal Difference/Points I will be using Excel 2002. Many thanks...Gracust -- gracust ---------------------------------------------------------------------...

Calculated Field in Pivot Table
Hi, How can I create a coloum in a pivot table that is the sum of two others coloumns? Thanks, Tamir :-) EXCEL 2007 Click in the Pivot Table / PivotTable Tools / Options / Tools group / Formulas / Calculated Field / complete the Name: field / enter the Formula: field and remove the 0 / Click the first Field: you wish to add / Insert Field / enter the + sign / Click the second Field: you wish to add / Insert Field / OK If my comments have helped please hit Yes. "Tamir" wrote: > Hi, > How can I create a coloum in a pivot table that is the ...

An image as a backgroud for a table in a template
I want to have an image as a background for a table in a template. Then i want to add text boxes in each cell. The image will then be as a help for users were to put an X on the image. I use Word 2003. Insert the image and format it as Behind Text. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Roy" <Roy@discussions.microsoft.com> wrote in message news:C9FEDB40-3F6F-4B2E-88C9-ABCD56EC99BD@microsoft.com... >I want to have an image as a background for a table in a template. Then i > want to ad...

Pass table data from Access to a C dll and get it back
Hi, I want to pass a table's data into C code for further processing and get the processed data back into another table in Access. I have seen sample code in C++ opening an Access db and using automation on it. However, this is not permitted in my case; the driver application has to be Access itself. So maybe I have to pass the data into VBA and then further into the C code? Sample code for this anyone? Just a simple table with a few columns (properties) will do. TIA, Nikolas "rdrnws" <rdr@nws.com> wrote in message news:fh9nfp$rpo$1@aioe.org... > Hi, I want to ...

formula to search and find item sheet1 copy to sheet 2
i need to find an item in a colunm on sheet1 and auto copy that items row of info to sheet2. sheet1 changes with each import of data as to where "wings" may be on line 27 one time and move to 29 on the next import. so i need to search colunm B for "wings", when "wings" is found copy that row of info to sheet2 to create a list on sheet2.the formula will need to be adjustable per line, where as i can just change the item "wings" to "burgers" then insert the formula into line 2 sheet2.this will allow sheet2 to locate "burgers" on sheet1 ...

Help with setting a formula
I am attempting to setup two columns. One column is adding charges. The other is showing the charges deducted from a starting balance. I am trying to keep track of a cash card as I am spending the funds. -- Thanks in advance for your assistance HankL Put your initial balance in A1 and nothing in B1 In A2 enter: =A1-B2 and copy down As you enter charges in B2 and going down, they will be debitted in column A and the balance will appear. -- Gary''s Student - gsnu200791 "HankL" wrote: > I am attempting to setup two columns. One column is adding charges. The ...

List from a Table
Hi, Suppose i have some data across 2 columns starting A2 and B2. Owner of car, and Car Model respectively. Column A may contain repeated entries (Owner can have more than 1 car of different companies AND can also have 2 same cars. ) B also contains repeated entries. abc ford def ford xyz nissan xyz suzuki efg honda abc toyota I want to enter in C1 name of the owner and get list of cars he has across C2:C5000. Same in D1, name of car and get list of owners across D2:D5000. ...

Time Table
hi members i want to develop a timetable for teachers and their periods. i want there should not be timeclash -- b166e ----------------------------------------------------------------------- b166er's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3491 View this thread: http://www.excelforum.com/showthread.php?threadid=57376 What have you done so far? Where are you getting stuck? If you provide some details, someone may be able to help. b166er wrote: > hi members > > i want to develop a timetable for teachers and their periods. > i want there sh...

Speeding up caluclation of array formula
I have a large sales analysis spreadsheet, which looks up the sales value for a customer by year, quarter, month, sales rep by using formulas such as the one below. It is taking a long time to calculate any changes. Is there anything I can do to speed it up. =SUM(($A9='MASTER DATA'!$A$2:$A$2500)*(F$5='MASTER DATA'!$N$2:$N$2500)*(F$4='MASTER DATA'!$M$2:$M$2500)*(F$6='MASTER DATA'!$L$2:$L$2500)*(F$7='MASTER DATA'!$F$2:$F$2500)*'MASTER DATA'!$G$2:$G$2500) Thanks, Esther Ester, Try using pivot tables instead of monster array formulas. F...