Pivot Tables question

I have two colums of data such as:

French   /         Male
British     /       Female
Chinese   /        Child
French       /      
British       /      Male
Chinese     /               
French     /
French      /       Female

I create a pivot table that shows me how many instances of blank (n
data) in column 2 for each item in column 1 (nationality), in this cas
French 2 times, Chinese 1 time.

The question is the way I have it set up I have to un-check (100
lines) all of the instances of male/child/female etc.  in order t
leave only the Blank instances.  How can I just choose to see only th
blanks rather than un-check 1000 instances of the rest?

Thanks

--
Message posted from http://www.ExcelForum.com

0
6/9/2004 2:08:42 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
360 Views

Similar Articles

[PageSpeed] 9

dziw,

You can use COUNTIf for a count of only blanks, something like:
=COUNTIF(B1:B20,"")

Or for a variable-length list, this counts blanks in column B only where
there's something in column  A:
=SUMPRODUCT((A2:A65536<>"")*(B2:B65536=""))
I think there's an easier way, but that neuron won't fire this morning.
-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"dziw >" <<dziw.17l70g@excelforum-nospam.com> wrote in message
news:dziw.17l70g@excelforum-nospam.com...
> I have two colums of data such as:
>
> French   /         Male
> British     /       Female
> Chinese   /        Child
> French       /
> British       /      Male
> Chinese     /
> French     /
> French      /       Female
>
> I create a pivot table that shows me how many instances of blank (no
> data) in column 2 for each item in column 1 (nationality), in this case
> French 2 times, Chinese 1 time.
>
> The question is the way I have it set up I have to un-check (1000
> lines) all of the instances of male/child/female etc.  in order to
> leave only the Blank instances.  How can I just choose to see only the
> blanks rather than un-check 1000 instances of the rest?
>
> Thanks!
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
nowhere1083 (630)
6/9/2004 2:56:29 PM
Reply:

Similar Artilces:

Exchange 5.5 EDB questions
Hello, I have an exchange 5.5 server, which is running on Windows 2000 server. I have set it up so that I can try to recover some data from a stray pub.edb file. I have tried using the edbutil/eseutil to try and recover the database and change the headers, but I cannot find a definitive answer to the following question. 'How do I import a foreign PUB.EDB folder into my exchange database, and allow users to access it as a folder (Public)?' Thanks in advance, James Copp-Taylor You do not import .edb into Exchange database. You perform disaster recovery, export data to .pst, then imp...

Multi Table Queries
At my job, I use enter data through generated queries. However it appears that I can only ENTER data in a query when it is based on two tables. If it is three or more, it doesn't let me enter any info. Is this normal? Am I doing something wrong? -Pete Pete wrote: >At my job, I use enter data through generated queries. However it appears >that I can only ENTER data in a query when it is based on two tables. If it >is three or more, it doesn't let me enter any info. > >Is this normal? Am I doing something wrong? Yes, that's common. You should only edit data ...

Macro Question Again
I'v never had any dealings w/ macro before, so this is all new to me. I have a windows 98 computer that is running office 2000 and there is an excel macro on this pc that was created by someone years ago. I have to get this 98 machine of the network and replace it w/ a 2000/XP platform also running either office 2000/xp. So far i'v copied the macro and am able to run it, but its not gathering the correct info. It is collecting the correct info on the 98 machine, just not the one im replacing it w/. It looks like some of the fields are supposed to be populated w/ info in a text d...

Question on Returns to Vendor without PO & Receipt number
Our company switched to Great Plains Dynamics from Simply Accounting 3 months ago. We currently need to return some of the inventory items to our vendors. However, when we try to enter a transaction entry in Purchasing/Returns Transaction Entry, in the detail lines we are asked for the PO number or the receipt number. We are not allowed to go further or save without the PO number or receipt number. When we converted from Simply Accounting, all we entered was the beginning balance of the vendors and only the PO's which we expect to receive. The older PO's were not carried over. W...

Unique records in Pivot Tables
Excel 2000 I have data that stores Client Invitation to Tender and Supplier Contract details. Each Client ITT is uniquely identified. However we send out the same ITT to many suppliers so each request for service is given the same identifier, eg Reference Client/Supplier Contract Name Value Status AC/001 Client blah 4,000 pend AC/002 Client blah2 5,000 pend AS/001 Supplier blah3 3,000 pend AS/001 Supplier blah3 3,000 pend When I create my table by Client to get the total value pending ...

Licensing Question with Professional Suite
Hey, I've seen some licensing questions out there, but wanted to be clear. Microsoft Reps are giving me different information, but admit they do not know!? I think there needs to be a little more training with the CRM licensing. I need the CRM Professional Suite (sales and service). If I have 5 sales people (that don't really need to get into the Service Stuff), and I have 5 Service People (that don't need to do anything with Leads and other stuff), can I purchase 5 Sales Professional Users and 5 Service Professional Users? Or do I still need to purchase the Suite User...

Questions about Migrating from Outlook Express to Outlook
I'm thinking about migrating from OE 6 to Outlook 2003 and was wondering if I will lose my folder structures, rules, and address book by doing so. I also use Yahoo POPs for importing my Yahoo e-mails into OE. Will I need to reconfigure the Yahoo POPs to work with Outlook after the migration? Thanks in advance for your help! Spudy Not sure about the YahooPOPs thing but using Outlook's File menu > Import & Export command should get most of the data. You can also try exporting from OE. "Spudy" <spudy@spamless.com> wrote in message news:th1hd1ddvnto8he4fsv9mluf...

Employee Master Table error after V10 SP3
i have updated V10 to SP3, from SP1, and all seems to be fine except if I try to access the employee master table. Either from cards>payroll>Employee, or payroll transaction entry. Anytime I need to do a lookup on an employee id. Here is the message: A Get Change operation on table UPR_MSTR failed accessing SQL Data If I go to the more information or details button: [Microsoft][SQL Native Client][SQLServer] Invalid column name 'EMPLSUFF' [Microsoft][SQL Native Client][SQLServer] Invalid column name 'Dex_Row_TS' -- Doug It looks like an upgrade script failed...

IF question...
I need a formula which will allow me to accept a number if it falls within a certain range. For instance, I want the cell to recognize the number in the cell if it falls between 1 and 10. Otherwise, I want the cell to return a 0 value. Anybody? =IF(AND(A1>=1,A1<=10),what?,0) On Mon, 25 Oct 2004 19:15:06 -0700, "mailrail" <mailrail@discussions.microsoft.com> wrote: >I need a formula which will allow me to accept a number if it falls within a >certain range. For instance, I want the cell to recognize the number in the >cell if it falls between 1 and 10....

Pivot table novice
As a teacher we tried a new data analysis tool this year in my school which worked really well... except for the fact that it meant manually filling in a table. I'm sure there must be a more efficient electronic way of doing it, but I'm not really an expert on these things. I want to be able to put in a list of data: pupils names with scores from two consecutive tests. Then I want to produce a table with test 1 and test 2 as the headers, and pupils' initials appearing in the relevant box. I have managed to create a Pivot Table to show what I want, except of course, it total...

Outlook macro question
I have two email accounts in Outlook 2003. I know about the Accounts drop list in new emails. But is it possible to create a few macros so that I end up with two buttons where each runs a macro that starts a new email using a specific account? One button would start a new email using account 1 and the other button would start a new email using account 2. If so, could someone please give me some pointers? Thanks. Outlook doesn't provide any direct way to change the account for an = outgoing message in versions before Outlook 2003. See=20 http://www.outlookcode.com/codedetail.aspx?id=3D88...

Pivot Chart: Stop skipping Dates on X-Axis
I have a pivot chart which pulls 2 pieces of information from one table 1 and 1 from Table 2. All three entries are included for each day. Except somedays which may have only the one piece of data from Table 2 or none at all. How do I get the table to automatically fill in all the days whether they have information or not? =?Utf-8?B?UHJpbmNl?= <Prince@discussions.microsoft.com> wrote in news:A5EF53AB-6063-434F-BFC3-43259301751D@microsoft.com: > I have a pivot chart which pulls 2 pieces of information from one > table 1 and 1 from Table 2. All three entries are included for &...

Stack overflow question
Occassionally when on Facebookwith MSIE if I have 2 instance of Facebook open (Different pages) I will get an error message of "Stack Overflow at Line: 31". When I click OK the page finishes loading fine. Any suggestions as to what is going on? Running: <<< System Summary >>> > Manufacturer : eMachines Inc. > Mainboard : eMachines WMCP61M > Chipset : nVidia GeForce 6100V > Processor : AMD Athlon Neo MV 26 @ 1600 MHz > Physical Memory : 4096 MB (2 x 2048 DDR2-SDRAM ) > Video Card : NVIDIA GeForce 6150SE nForce 430 ...

Easy question on Excel...
Hello I have an Excel workbook open which has 5 worksheets. How do I toggle through them manually without using the mouse to click a sheet? I am sure I used to know how but now I forget! Regards Alex Try Ctrl + PageUp Ctrl + PageDown Regard -- Message posted from http://www.ExcelForum.com Well done. Thanks. >-----Original Message----- >Try > >Ctrl + PageUp > >Ctrl + PageDown > >Regards > > >--- >Message posted from http://www.ExcelForum.com/ > >. > Ctrl + Page Up or Page Dow -- Message posted from http://www.ExcelForum.com Hi I th...

Pivot charts formatting
Hi, I want to change the formatting imposed by default in a pivot chart and I want that changed formatting to be retained permanently. For example, for my bar chart, for the different series I have selected colours / patterns but every time I refresh the pivot table / chart; excel falls back to its own colour coding. Any way to get around this? Many thanks in anticipation. Not directly. The only way to simulate this is to record a macro of the formatting changes that you make and then set it up to run whenever the PivotChart calculates. Assuming your pivot chart is on a seperate shee...

Combining Pivot Tables
Hi All, I have a data set of around 100,000 rows which I have imported into excel in two sheets (~50,000 rows each). The data is not in a format that excel can easily parse into a pivot table directly from the source - it requires some formulae in excel to be able to use a pivot table. I have used a pivot table on each of those sheets to summarise the data, and that works fine. However, I would like to be able to get a single summary pivot table from the two sheets (or from the two pivot tables). Is that possible, and if so, how do I go about it? Thanks, Alan. "Alan" <...

Filtering data by custom fields in pivot tables from Visual Report
I have recently upgraded to MS Project 2007 and like the visual reporting feature, except I really need to filter and group the resulting pivot table in Excel by custom fields. For example, for task usage, I cannot filter my pivot table to show only the tasks that have costs > $0, thus my table ends up showing too many lines and a lot of 0's. I would also like to group by department/ function which I put in a custom text field. How do I do this? I saw related posts about saving the file as an Access database and then creating the pivot table by connecting to the datab...

Button Control Event: question
I have a small dialog that prints data it queries from a server to a listbox when a user presses a start button. What I'd like to do is to have the data continually print after the button is pressed until the user presses a stop button. So, I guess I would need to initiate some kind of continuous loop for the data querying/printing, but I'm unsure how to get the stop button to break the loop. If this even the best solution. Does anyone have any ideas of how I could accomplish this, or perhaps a better design idea? Thanks, Marcus Marcus wrote: > I have a small dialog that prints d...

Excel template and access questions
We are going to use the Excel purhase order templates. We need to save each order in order to refer back to it. Is there a way to do this without saving each individual file (order)? Can we save in some sort of easy to locate "batch"? Also, is there a way to transfer a template that we have done (and all the data realted to it) into a Acess database withouthaveing to so much tweaking? If you have Access, I would suggest creating your purchase order database in Access and then having a mail merge push this information into an Excel template as needed, rather than going the othe...

Help to build a table
Please help me to build a table similar to this one The number on the first column will change after 16 times, it will go in sequence from 1001, 1002, etc… The number in the second column will change after 4 times and it will go from 1 to 4 The number in the 3rd column will go from 1 to 4 2nd and 3rd column will keep the same pattern. Cabinet Shelf Location 1001 1 1 1001 1 2 1001 1 3 1001 1 4 1001 2 1 1001 2 2 1001 2 3 1001 2 4 1001 3 1 1001 3 2 1001 3 3 1001 3 4 1001 4 1 1001 4 2 1001 4 3 1001 4 4 1002 1 1 1002 1 2 1002 1 3 1002 1 4 1002 2 1 1002 2 2 1002 2...

Simple question about text within a cell
I can't seem to find the option that prevents text from covering the adjacent cell. For example if I type the folling in cell A1: All I want for Christmas is my two front teeth. Obviously that will overflow over the cell B1 (if there is no data in B1). I don't want to resize my column, I only want the cell to show as much text in A1 as possible without covering cell B1. I believe I've seen the option to do what I'm looking for but I can't seem to find it and I can't figure out how to look it up in the HELP file. Thanks in advance. Rick My simple solution ha...

Grand Substraction instead of Grand Total in pivot table
Hello, I created a pivot table in Excel with Visual Report 2007. I'm using an Outline Code with 2 leaves : Receipts and Expenses. The pivot table created automatically a Grand Total so that $1000 Receipts and $800 Expenses => Grand Total = $1800. My accountant says $200. Generally, it's possible to create calculated fields in the Excel pivot tables. But in the pivot table created by the Visual Reprot, all the Calculated Field options are greyed out. Why ? Thanks for any help I don't know what visual report is, but suspect that it would be more a question for them not Excel a...

Windows Pro C.O.A. Question
I have a Dell Latitude D510 with Windows XP Pro SP2 installed. It's used and the machine didn't come with a restore disk, and there is no restore partition. But the COA sticker is attached to the bottom of the machine. I have an OEM copy of Windows XP Pro with SP2. If I have to reinstall the operating system, can I use the OEM version and use the laptop's COA to activate Windows? I realize that I may have to hunt down drivers such as WiFi and sound if they aren't included with my OEM copy. Thanks Don wrote: > I have a Dell Latitude D510 with Windows X...

Some stupid questions about edit the aspx file
Hi, I'm quite newbie about asp.net & other web application. I've heared that we can put some code in the CRM aspx file. For example, we can make OnChange event to the textbox in CRM & put some code in it. I know it is unsupported by microsoft, but I need to know how to do it. I've been searching this newsgroup & microsoft.public.crm, but I couldn't found any sample. My question is pretty simple (or stupid, I guess): I've looked in inetpub\wwwroot\SFA\quotes\edit.aspx, but I don't know what to do. My friend telling me that the code behind, is under som...

Address Book renaming contacts. (2 questions)
For WHATEVER reason Outlook 2002 - XP is renaming my contacts in my address book.. so when I try and use autocomplete it doesn't work. (Type in "Sha" to get the choice between Shadow and Sha'mer and it just gives me Sha'mer) So - gritting my teeth - I go in and change the name of the contact BACK to Shadow and it STILL doesn't register in the autocomplete. 1) How the (ahem) do I get Outlook to stop renaming my contacts? 2) How do I get it to recognize names in the autocomplete? I now have about 7 that it doesn't, and it's a royal pain. Thanks for your tim...