Count(s) in Pivot Table

I have listing of 1600 stores and sales by 4 week periods.  I have a helper 
cell to combine the state and city since this is one basically how I want the 
date to break.  But there may be say 15 unique stores in Atlanta, my pivot 
shows the correct total sales for Atlanta but in the "count" I am getting 
instead of 15 I expect it is counting the each store in each period (15 
stores x 12 periods=180).

in the pivot I want the state, city total sales, unique stores (making up 
the total) so that it would it would look like:
GA: Atlanta 5000 (sales) 15(unique stores)
0
Utf
1/11/2010 10:30:01 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
819 Views

Similar Articles

[PageSpeed] 39

It is counting the number of records that have a value for stores not a 
unique count of the stores. Check out this link...

http://www.contextures.com/xlPivot07.html#Unique
-- 
HTH...

Jim Thomlinson


"Reno" wrote:

> I have listing of 1600 stores and sales by 4 week periods.  I have a helper 
> cell to combine the state and city since this is one basically how I want the 
> date to break.  But there may be say 15 unique stores in Atlanta, my pivot 
> shows the correct total sales for Atlanta but in the "count" I am getting 
> instead of 15 I expect it is counting the each store in each period (15 
> stores x 12 periods=180).
> 
> in the pivot I want the state, city total sales, unique stores (making up 
> the total) so that it would it would look like:
> GA: Atlanta 5000 (sales) 15(unique stores)
0
Utf
1/11/2010 10:36:01 PM
Excel 2007 PivotTable
Count unique stores.
With and without formulas.
http://www.mediafire.com/file/rzy4vmjtwnk/01_11_10.xlsx
0
Herbert
1/12/2010 4:13:52 AM

"Herbert Seidenberg" wrote:

> Excel 2007 PivotTable
> Count unique stores.
> With and without formulas.
> http://www.mediafire.com/file/rzy4vmjtwnk/01_11_10.xlsx
> .
> i beleive this will do it, but i don't see how to Insert Calculated Field, it does not appear on the Insert Ribbon, my ignorance is stunning.
0
Utf
1/12/2010 10:57:01 PM
Excel 2007 PivotTable
Count unique.
Added more pictures.
http://c0444202.cdn.cloudfiles.rackspacecloud.com/01_11_10.xlsx
0
Herbert
1/13/2010 4:18:47 PM
Reply:

Similar Artilces:

Vlookup on multiple table array
there is a summary source, i have 6 different sources. i want to lookup from the summary source whether the 6 different sources exist in the summary? may i know how to lookup multiple table arrays? thx. I'd just use 6 different cells and use a formula that points at the 6 different sources. There is no =vlookup() trick to look through multiple lookup tables. Winson wrote: > > there is a summary source, i have 6 different sources. i want to lookup from > the summary source whether the 6 different sources exist in the summary? may > i know how to lookup multiple table array...

Sorting numbers into ranges i.e 0-1000,1001-2000 and getting count
Please help, I need to sort the following type cost apple 800 banana 1000 cherry 1500 banana 1050 banana 600 cherry 2500 apple 0 so that it sorts by type and then does count between ranges 0-1000 1001-2000, 2001-3000 i.e type 0-1000 1001-2000 2001-3000 apple banana "with a count here " cherry thanks mik -- Message posted from http://www.ExcelForum.com Hi one way: 1. Add a helper column to show your type. e.g. enter the following in column C / cell C1: =MAX(INT((B1-1)/1000),0)*1000 & " - " & (MAX(INT((B1-1)/1000),0)+1)*1000 and ...

ObjectTypeCode in Entity table
Do you know the function of ObjectTypeCode attribute in Entity table in _metabase db? I have noticed that for some records the relative value is 0, for a CRM 1.2 installation, but not for the same table for a CRM 3.0. Many thanks The ObjectTypeCode determines the type of entity in 3.0. For example, 1 = account, 2 = contact. This code appears throughout the application as a quick and easy way to identify the type of entity being referred to. A complete mapping can be found by running the following query select objecttypecode, name from _metabase.dbo.entity order by objecttypecode ...

Changing sender's name in Outlook Express
The Outlook Express program for our Lodge was set up by our former secretary. HIS name appears as the sender on all email I send out. How can I change this so that our Lodge name will appear, and not any individual's name? Would appreciate an email reply to lodge99@(remove)cell2000.net as well as a posting if possible. Thanks! Mike You can change this in your account settings; Tools-> Accounts Note that this is an Microsoft Office Outlook forum. Outlook Express is family of Internet Explorer and Outlook of the Office family. Here is the link for the right forum http://com...

GDI Objekt's Leak in a MDI environment
I'm using Visual Studio 6.0 SP 6 I create a VisualStudio C++ Projekt, MFC, MDI with or without Document Support... I don't add any code for my own, i use just the automatically generated code. When I create client windows, every window will consume 6 additional GDI-Object's. When I close the client window, the GDI - Object's stay alive, and so I'll get more and more Objetcs. Has anyone the same experience ? Where is the problem ? Thanks for every help. >When I create client windows, every window will consume 6 additional >GDI-Object's. When I clo...

Distribute tax & freight based on this invoice's allocations
Allow a hot key (such as "*") to apply any indirect charges against all the prior line items. With this, instead of multiplying the line item times tax and freight for each line of expense and entering the result, you enter the expense amount directly and on the next entry line enter the hot key ("*"), now the software multiplies it out and adjusts for rounding. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane....

pivot tables #12
Can't understand why it's so difficult to create a pivot table report. I've followed all the tutorials I could find on google-including Microsofts', none of them work for me. Isn't there a simple way? It's so easy with MS Works. What version of Excel are you using? What problem are you having when creating a pivot table? herbzee wrote: > Can't understand why it's so difficult to create a pivot table report. > I've followed all the tutorials I could find on google-including > Microsofts', none of them work for me. > > Isn't the...

it's not autofilling address from contact list
I add a sender to the contacts, but when I type their name into the field for a new email, it does not auto fill in like others. Their name never appears. Others appear from contacts, but not this one. The first/last name fields are filled in. Any suggestions? Thanks. Of course not. Autocompletion doesn't use your Contacts. -- Russ Valentine [MVP-Outlook] "Bryce" <fukuzz@hotmail.com> wrote in message news:45ac25cb$0$10303$815e3792@news.qwest.net... >I add a sender to the contacts, but when I type their name into the field >for a new email, it does not auto ...

Copy to CString's internal buffer
Hi All, I have a function like this: void func(char* buffer); I know that this buffer is 20 characters long; Does anybody know why I can't do the following in the body of the function: CString str; LPTSTR lptStr = str.GetBuffer(20); memcpy(lptStr, buffer, 20); lptStr.ReleaseBuffer(); I don't understand why I get "?????" in my CString's buffer (while still within the scope of the function). However, if I do the following, it works: char buff[21]; CString str; memcpy(buff, buffer, 20); buff[20] = '\0'; str = buff; The reason why I am trying to use mempcy dire...

Count of text in cells
I have problem to solve,I have a column with office. The text in the Office cells with be, Office 1@810@NT but I now have a new columm with text in the following format:Test The Cell - Employment cells can contain mutiple values, Test The Cell - Education;Test The Cell - Employment;Test The Cell - Health . These values are separated by a ; NOt only do I need to count each occurance, but I then need to have a count of each one. I can have these values referred to in a cell if that makes it easier. Sorry if I have not been clear Submitted via EggHeadCafe - Software Developer Portal of Ch...

Click on graph bar to execute a double-click in a pivot table cell
Excel 2002. I have a group of 6 different stacked bar charts built from parts of a pivot table. Each graph has several bars with each bar having sections for categories "10", "20", "30", "40", and "50". The graphs can be direct linked to the pivot table (as a pivot chart) or linked to an intermediate sheet looksup the data from the pivot table. I want to be able to click (double or single click) on a graph bar or section of a bar and execute the same action (.ShowDetail) that occurs when you double-click on a cell in the pivot table (print th...

Groupings in pivot tables?
Dear All, I love pivot tables and the grouping function but I would like to know if I can do this... I have a large source of data it contains these fields Call ID, Date call raised, date call resolved, Team, then a formula field called 'time taken to resolve' which is Date call resolved - Date call raised. I then have a pivot table with Team in Row, Time taken to resolve then in Data Count of Call ID. Because there is so much data I have grouped this by 20. Is there a way where the grouping can be variable and not the one set amount? Thanks -- Adam ----------- Windows 98 + Offic...

Hiding Excel window's scrollbars
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hello, <br><br>I'm updating an old Excel spreadsheet I created a long time ago. Somehow, I was able to hide the spreadsheet window's scrollbars. I did this because I wanted the user to focus on a specific area of the spreadsheet and not scroll around. <br><br>I would like to unhide the window's scrollbars while I edit the sheet. And I would like to hide them again when I save the sheet for others to use. <br><br>I searched this forum and found nothing on this topic...

Pivot from multiple sheets 05-28-10
hi, i have the below data and i need to make pivot from multiple sheets.i have tried to make by selecting multiple consolidation ranges, bbut it is not giving me the flexibility that is available while doing in a single sheet... Month Processor Process Jan Mark Manual Feb Henry Automatic Jan Chad Automatic Mar Mark Automatic Apr Mark Manual Jan Chad Automatic Jan Henry Manual Apr Henry Manual Feb Mark Automatic Mar Chad Manual Jan Mark Automatic it gives the belowformat... Row Process Processor Grand Total Jan 15 15 30 Feb 6 6 12 Mar 6 6 12 Apr 6 6 12 Total 33 33 66 ...

Use of table and Scenerio in data and tool menu
what is the use of table command in data an Scenerio command in tool menu -- Sajjad ...

script to count the number of mailboxes on all storage groups on a single mail server? #2
Gurus, Is there a script I can run which can count the number of mailboxes on all storage groups on a single mail server? ...

Strange Ctrl-S behavior in Excel 2007 (Danish locale)
Hi I've just bought and installed Office 2007 Enterprise and I'm experiencing some strange things in Excel. For example, when I press the Ctrl-S key combination, which would normally just save the sheet, I get a warning (exclamation mark in yellow triangle) dialog box with the text "No cells were found" (translated from Danish: "Der blev ikke fundet nogen celler.") and only an OK-button is present. When I press OK, nothing happens. I have no problems saving the sheet using the toolbar or menu. I'm running Windows Vista Home Premium Danish. Has anybody experi...

tracking from a query to a table
I have a table that contains codes for our distance learning classes. Our state is getting ready to make a massive change to these codes. I know I have queries that support reports and forms based on these codes. Is there a way to track from the table to each query, form, or report that the codes is used? Any help here will really save me a lot of hours tracking this stuff down or waiting until something breaks. Thanks Jacqueline On Wed, 27 Jan 2010 15:24:01 -0800, Jacqueline <Jacqueline@discussions.microsoft.com> wrote: >I have a table that contains codes for...

Which Function(s) should I use?
Hi All, I want to generate 5 lists using the preference column from raw data which is in the following format: Preference Name Club 1 A Smith NY 2 G Murphy WN 3 S Rogers MA 4 B Wall CN 5 C Hill DW 1 D Thomas ON 2 W Shatner CA 3 E Jones MS 4 L Long SC 5 J Downe NC Can you please advise which functions I should use to collate the data, also allowing me to cha...

Duplicating records from one table to itself
I have developed a data entry form for my customer's accounting application. This for has a controlsource that is the accounting transactions table. I have been asked to provide a capability to copy the record being displayed on the screen and duplicate it a number of times in the same table as recurring transactions, each of which would have a different billing date. I have considered several approaches, but would like some suggestions about how to proceed. Thank you. You can create an append query that has the form controls for the value of each field. Then you can...

Adding a Field Name from Table/Query to Excel Export File
What I have is an export file to excel which I am able to do successfully using an OutputTo macro: Object Type: Query Object Name: ZZZ_Executive_Summary Output Format: Miscrosoft Excel 97-2003 (*.xls) Output File: "C:\ES\" & Format(Date(),"mm-dd-yyyy") & " Executive Summary. xls" What I would like to do is add the branch name into the file name. The field in the query is simply "Branch" When I try something like &[Branch]& or (query name/field) >> &ES!Branch& or &[ES].[Branch]&, etc... no matter what I...

display all row fields in pivot table
I have a pivot table which groups my row fields and shows blank cells. I want to show all field cells. Basically, I have: Sales Bob Client ABC Contract 1 Project 1 <blank> <blank> <blank> Project 2 <blank> <blank> Contract 2 Project 1 <blank> Client XYZ Contract 1 Project 1 I want to display as: Sales Bob Client ABC Contract 1 Project 1 Sales Bob Client ABC Contract 1 Project 2 Sales Bob Client ABC Contract 2 Project 1 Sales Bob Client XYZ Contract 1 Project 1 Is this possible? Meant... =if(len(a2)<>0,a2,f1 Sorry! -...

Macro to add value if cell has a color :-s
I don't know if this exists, but is it possible to check if a certain cell has a color? Normally a cell is blank, but someone asked me to see if it is possible to check if a cell is colored.. Any suggestions? Thanks!!! Is this sufficient? Sub ifcolor() If Range("b2").Interior.ColorIndex > 0 Then MsgBox "yep" End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com <test@testnospam.nl> wrote in message news:obht5456aq6ur5gbqmasacqke9p2g13o7i@4ax.com... >I don't know if this exists, but is it possible to check if a cer...

Pivot Table
Hello, We have a report that is based on a pivot table that is linked to a MS Access database. This database is located in a drive in our network drvie that stopped working. We do have a backup of the database but will restore it in some other location. Is there a less painful way to point the pivot table source database to another database than to recreate them? Thank you in advance. Brigham Hi Brig Right click on the PT and select Wizard>Back>Enter source of new data>Finish>Refresh -- Regards Roger Govier "Brig Siton" <bsitonno@spamoptonline.net&...

Pivot Tables
I have external data comming in from a database, when i alter the data the pivot table still shows row selections from the old data as well as the new, is there any way to reset the list without removing and adding back in the table. I cant find where in the spreadsheet it stores this information. Steve There are instructions here for clearing old items from the dropdown lists: http://www.contextures.com/xlPivot04.html Steve wrote: > I have external data comming in from a database, when i alter the data the > pivot table still shows row selections from the old data as well ...