Count missing data

Hello,

I would like to count missing data in a Column range due to the criteria in
another column range. The problem I have is that the "Birth Date" column
range has a "vlookup" formula in it, which sometimes formulates a Blank. I
need to count these Blank cells when the Status = "A".

Example:
Birth Date       Status
02/03/71        A
                      A
12/02/58        A
09/15/67        A
                      A
11/21/79        A

Missing = 2

Thanks
Ruan


0
ruan (28)
6/23/2004 8:14:43 PM
excel.newusers 15348 articles. 2 followers. Follow

5 Replies
527 Views

Similar Articles

[PageSpeed] 34

Try this:

=SUMPRODUCT((A2:A100={" ",""})*(B2:B100="a"))
-- 

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Ruan" <ruan@aegismed.com> wrote in message
news:Ou9j56VWEHA.1340@TK2MSFTNGP10.phx.gbl...
Hello,

I would like to count missing data in a Column range due to the criteria in
another column range. The problem I have is that the "Birth Date" column
range has a "vlookup" formula in it, which sometimes formulates a Blank. I
need to count these Blank cells when the Status = "A".

Example:
Birth Date       Status
02/03/71        A
                      A
12/02/58        A
09/15/67        A
                      A
11/21/79        A

Missing = 2

Thanks
Ruan


0
ragdyer1 (4060)
6/23/2004 8:24:22 PM
Thanks for your help that works great. I have never seen the brackets { }
used before. What is the difference?

Ruan




"RagDyer" <ragdyer@cutoutmsn.com> wrote in message
news:%23QQCwGWWEHA.1756@TK2MSFTNGP12.phx.gbl...
> Try this:
>
> =SUMPRODUCT((A2:A100={" ",""})*(B2:B100="a"))
> -- 
>
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
>
> "Ruan" <ruan@aegismed.com> wrote in message
> news:Ou9j56VWEHA.1340@TK2MSFTNGP10.phx.gbl...
> Hello,
>
> I would like to count missing data in a Column range due to the criteria
in
> another column range. The problem I have is that the "Birth Date" column
> range has a "vlookup" formula in it, which sometimes formulates a Blank. I
> need to count these Blank cells when the Status = "A".
>
> Example:
> Birth Date       Status
> 02/03/71        A
>                       A
> 12/02/58        A
> 09/15/67        A
>                       A
> 11/21/79        A
>
> Missing = 2
>
> Thanks
> Ruan
>
>


0
ruan (28)
6/23/2004 9:25:22 PM
Another way

=SUMPRODUCT(--(TRIM(A2:A100)=""),--(B2:B100="A"))



-- 
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"RagDyer" <ragdyer@cutoutmsn.com> wrote in message
news:%23QQCwGWWEHA.1756@TK2MSFTNGP12.phx.gbl...
> Try this:
>
> =SUMPRODUCT((A2:A100={" ",""})*(B2:B100="a"))
> -- 
>
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
>
> "Ruan" <ruan@aegismed.com> wrote in message
> news:Ou9j56VWEHA.1340@TK2MSFTNGP10.phx.gbl...
> Hello,
>
> I would like to count missing data in a Column range due to the criteria
in
> another column range. The problem I have is that the "Birth Date" column
> range has a "vlookup" formula in it, which sometimes formulates a Blank. I
> need to count these Blank cells when the Status = "A".
>
> Example:
> Birth Date       Status
> 02/03/71        A
>                       A
> 12/02/58        A
> 09/15/67        A
>                       A
> 11/21/79        A
>
> Missing = 2
>
> Thanks
> Ruan
>
>


0
terre081 (3244)
6/24/2004 12:19:04 AM
Yours might be a little better Peo, considering mine only takes *one* space
into consideration.
-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" <terre08@mvps.org> wrote in message
news:uF2ibDYWEHA.4092@TK2MSFTNGP11.phx.gbl...
> Another way
>
> =SUMPRODUCT(--(TRIM(A2:A100)=""),--(B2:B100="A"))
>
>
>
> -- 
> Regards,
>
> Peo Sjoblom
>
> (No private emails please, for everyone's
> benefit keep the discussion in the newsgroup/forum)
>
>
>
> "RagDyer" <ragdyer@cutoutmsn.com> wrote in message
> news:%23QQCwGWWEHA.1756@TK2MSFTNGP12.phx.gbl...
> > Try this:
> >
> > =SUMPRODUCT((A2:A100={" ",""})*(B2:B100="a"))
> > -- 
> >
> > HTH,
> >
> > RD
> > ==============================================
> > Please keep all correspondence within the Group, so all may benefit!
> > ==============================================
> >
> >
> > "Ruan" <ruan@aegismed.com> wrote in message
> > news:Ou9j56VWEHA.1340@TK2MSFTNGP10.phx.gbl...
> > Hello,
> >
> > I would like to count missing data in a Column range due to the criteria
> in
> > another column range. The problem I have is that the "Birth Date" column
> > range has a "vlookup" formula in it, which sometimes formulates a Blank.
I
> > need to count these Blank cells when the Status = "A".
> >
> > Example:
> > Birth Date       Status
> > 02/03/71        A
> >                       A
> > 12/02/58        A
> > 09/15/67        A
> >                       A
> > 11/21/79        A
> >
> > Missing = 2
> >
> > Thanks
> > Ruan
> >
> >
>
>

0
ragdyer1 (4060)
6/24/2004 2:22:24 AM
"Ruan" <ruan@aegismed.com> wrote :
> ... =SUMPRODUCT((A2:A100={" ",""})*(B2:B100="a"))
> .. I have never seen the brackets { }
> used before. What is the difference?

Think the { } is functionally equivalent to an OR(), viz.:
Every cell in A2:A100 is checked to contain either " " or ""

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----


0
demechanik (4694)
6/24/2004 5:08:14 PM
Reply:

Similar Artilces:

Count if or SumProduct
I have two columns : column A defines the make/model; column B numerically defines how many were sold . Note: There are multiple entries for the same makes/models due to different territories. I have a separate worksheet for which I need to summarize this info. What would be the best formula for this task? I guess I am lost on what to enter to count. Any help is greatly appreciated! Thank you, Hi, Try this. The lookup value is in A1 of the currebt sheet =SUMPRODUCT((Sheet1!A1:A20=A1)*(Sheet1!B1:B20)) Mike "Brandy" wrote: > I have two c...

Sorting when data is on 2 rows???
I have data presented on rows as follows: 1 128655 Black, Robert 9:00 AM Breakfast meeting at Holiday 2 Inn. Casual. 3 123456 Blow, Joe 4:30 PM Customer wants to come 4 in to visit about a new 5 business venture. How can I sort Column 1 (128655 and 123456) so that Row 2 information stays with 128655 and row 4 and 5 stays with 123456? Can I be done? TIA, Ron As answered in...

Purchase Order Processing data has been damaged
After running checklinks and then reconcile on Purchasing Documents in GP 8.0, we received several Error messages as follows: "Purchase Order Processing data has been damaged; please rebuild the data." I have been unable to find any options or documentation on how to do this. Does anyone have any ideas? ...

File with packed-data
How do I convert a file containing packed-data to a file in ASCII-text format? For example: From: ....c.............00000000.. ........ 00087202320000000FFFFFFFF20408000103 00232C087C20C000C000000007202C00072C To: 0000283722 0028372 02000 0000000 270240028 000000017023 I don't think all your fields are packed. FFFFFFFF 00000000 Doesn't looked packed to me. And I think that a lot of FTP programs will "fix" some of the funny packed decimals when you transfer them to the pc (from a mainframe???). (I think they try to translate some rudimentary graphic characters to pc...

Mail Merge / Data Sources
Hi We have recently started using the Mail Merge Functionality in CRM 3.0. We have started to use the 'Print quote for Customer' button on the Quote form in the Outlook client and auto populate a Word quote template with CRM Quote Details. We have found that we cannot insert specifc product details on the Word quote as we can only insert fields from the Quote form. We cannot auto insert data from the Quote Products form or Contact form, for example. We have noticed that this could be because of the data source in CRM which the Word template is assocaited with. Is there a way ...

Two questions about converting data to SPSS
Hi, I've been having some problems trying to convert my data to SPSS from ACCESS. When I use DBMSCOPY, a file conversion program to convert from ACCESS to SPSS, the main data converts ok, but there are two shortcomings: a) The Field Descriptions from Access do not get converted to the 'Label' Fields in SPSS. b) The Look-up Fields in Access do not get converted to the 'Values' fields in SPSS. Is there a way to make these conversions happen? If there isn't, is there even a SIMPLE way of copying and pasting the descriptions from ACCESS to the SPSS labels that ...

Exporting excel data to text in word
I'm trying to export or copy/paste excel data into a word document as text. Is there a way I can do this without having comma or tab placeholders? I use copy/paste/convert steps to do this, and it's quick: 1) Select the Excel data, and use CTRL+C to copy to the clipboard 2) Open the Word doc to copy into and use CTRL+V to paste the data where you want it. The result is a table in Word. 3) Select the table using "Table>Select>Table" 4) Select "Table>COnvert>Table to Text"; choose the delimiter you want from the list, or enter a special character. ...

data merge excel source
Hi, I'm trying to set up an excel data source to merge into form letters, etc. I know how to do this on my PC at home, but I'm on a Mac in my new office and when I click on Open Source and my excel file, I get a message that "Word cannot open the file". But the documentation for Data Merge says very clearly that excel files are good sources. Has anyone solved this problem? Thanks. On 8/8/06 11:25 AM, in article 1155061531.652567.226460@n13g2000cwa.googlegroups.com, "ljf" <ljf2001@med.cornell.edu> wrote: > Hi, I'm trying to set up an excel data ...

Can you change the length of an excel chart data label?
The label is cutting off the number and I will need to change the length or scrap the label all together if the functionality is not available. Thanks for your help! A know bug. Some people have this problem other do not. Workaround: add some dots to the end of the title then format the dots to be invisible (in case others view the chart and do not have the 'bug') by making the font colour match the label's background colour (generally white) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Alia" <Alia@discussions.microsoft.c...

WMP lost all config data on every new start
Every time I start the WMP 12 it wants me to config it - it has forgotten all basic configs. OS win 7 Ultimate (update over vista home premium) wmp 12.0.7600.16415 - up to date On Wed, 11 Nov 2009 22:50:01 -0800, nitroklaus <nitroklaus@discussions.microsoft.com> wrote: >Every time I start the WMP 12 it wants me to config it - it has forgotten all >basic configs. >OS win 7 Ultimate (update over vista home premium) >wmp 12.0.7600.16415 - up to date If you go to Help -> About Windows Media Player, do you have the current released version or an earlier be...

Access data in a money file...
Hi all, First time that i post a message to that group.... I'm trying to access the data stored in a money file as i suppose it is stored in a database manner... Does anybody know of a way to do so through ODBC or something similar ???? thx laurent See http://www.bollar.org/msmoney/#Q10 and http://www.bollar.org/msmoney/#Q9. "Laurent Ades" <turkpipotutunu@ifrance.com> wrote in message news:6af9c352.0310100130.27458bdd@posting.google.com... > I'm trying to access the data stored in a money file as i suppose it > is stored in a database manner... > > D...

Manufacturing Data Collection should not require an Employee ID
When you setup a Work Center in the manufacturing module, specific Employee ID's are not required. You can enter totals for Effective Capacity, Overload %, and Overtime % without specifying any employees. Likewise, no specific Machine ID is required for the machine(s) used in the work center. You may enter just totals for the machine data. Yet when you enter a WIP Data Collection transaction against a MO for Direct Labor, the Employee ID field is required, and for Machine Cost, the Machine ID is required. In our situation, we may have three employees in a work center, but the...

Managing temporal data
Hi everybody, does anyone have an idea about how can I represent temporal data in Excel? I have data such as: Time Value 00:01 1 00:02 2 00:10 3 and I want to represent the series (time in the X-axis, and the value in the Y-axis). The problem is that "00:01", "00:02" and "00:10" are interpreted by Excel as categories, and they are equally spaced in the X-axis, despite there should be a larger space between "00:02" and "00:10" than between "00:01" and "00:02". (I already specified the first column as containin...

Data Validation
It's amazing how much time can be wasted trying variations of a custom formula in Data Validation without hitting on the right syntax :-( I have a cell (assume A1) formatted as a number to 2 decimal places. Normally a formula will enter the appropriate number in the cell, otherwise the formula will enter "". Sometimes the formula will be overwritten and a number will be entered manually. Because the number may be entered manually, I want to set a Data Validation check. Data Validation will need to allow both "" or any number (including 0.00). I was trying to...

Routing and Remote service missing
I have been having an on going problem with a VPN. The latest problem is that the service "Routing and Remote Access" has disapeared. How do I re-install the service? The RRAS wizard fails. Hello Skeelhaven, Which OS are you talking about, SP/patch level? What happens before your problem starts, new installation, crash enything else? Please describe more details about your setup, so we can really help you. Best regards Meinolf Weber Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights. ** Please do NOT email, only ...

Can't get any data to display in SSAS
I am using the Sql Server 2005 Analysis Services book and am doing the first cube. I can see plenty of data in the tables (such as dimProduct). But when I put the Measures, Date and Product from the left pane to the Browser window, nothing is displayed as the book says it should. Do I need to do something to tell it to display the data? Thanks, Tom I have no idea why this didn't work but the book is incorrect in a couple of areas (such as where is says to export when you are in affect going to import so that the default they say will be there are not there). ...

End of data definition
I have forgotten how to define an area of a worksheet as the end of file. I basically have 30,000 to 40,000 records but want to define different area as the end and home cells. IE if A1 to X40,000 is the data, I want to assign A3,500 as the home and X15,000 as my end while I work with this data. Any ideas? You could hide the unused rows and columns and set that as a View>Custom View you could select whenever you wish. CTRL + Home and CTRL + End will find A3500 and X15000 OR you could set the scrollarea via macro. Sub ScrArea() Sheets("YourSheet").ScrollArea = "A3500:...

PivotTable Based on large data
Hi, Using Excel 2000, we are trying to have PivotTable based on 130,000 rows of data. The problem is Excel has 65,000 row limitation. And I prefer to have PivotTable based og excel tablerather than OLAP cube. Two solution comes to my mind and I am wodering how practical are they: Solution 1) Upgrade to excel 2003, Because I am expecting excel 2003 have row capacity more than 65,000 rows. Am i correct? Solution 2) Have data in two different sheets, But i am not sure that we can have PivotTable based on Fragmented data in two sheets. Can I ? Could anyone tell me how practical are my two solu...

Automate data gathering from separate worksheets
I want to automate formulas on a dashboard worksheet. When I ente 'Fred' in cell A1 of the dashboard, I want all formulas in tha dashboard worksheet to pull data from the 'Fred' worksheet located i the same workbook. For example, I have a workbook with worksheets named 'Dashboard' 'Fred' and 'Bob'. When I enter 'Fred' in cell A1 of my dashboar worksheet, I want all formulas on the dashboard to reference data i the Fred worksheet as in "=Fred!C47" or "=SUM(Fred!X58:X62)". If enter 'Bob' in cell A1 of the dashboard,...

Database export, all data in one cell
I'm a member of an organization. For reasons we don't need to go into, I needed a list of our members. This is currently maintained in a database, and the secretary said she'd send it to me in Excel. Fine, I said. So I get it, and each members information is all in one cell: first name, last name, title, address, city, state, all of it. I need to sort by last name, but it is embedded in the middle of each cell. Any way out of this fix, short of manual cut and paste? Hate to do that with over 3,000 members. Thanks, Bob Hi if these values are separated by a delimiter fir...

Archive overwrites, not appends, to earlier archive data
There are 2 ways to archive, as far as the documented tools describe: auto-archive manual File\Archive In each case, my archive OVERWRITES the content rather than appending to it, as I would expect especially of the auto-archive. Are there nefarious registry settings that are causing the archive to behave badly, bluntly blowing away my e-paper trails? (Saaaay, maybe this isn't such a bad thing...) I do need to find this gremlin and dispatch him quickly. thanks ...

Data Migration Framework Doesn't Create cdf Tables for Custom Entities
The subject pretty much says it all. I'm running CRM 3.0 on a Windows 2003 R2 server with SQL 2005 installed. I have installed the CRM Data Migration Framework and run the CDF Initialization Wizard. After doing so, the cdf database is created and there are tables present for all of the default entities. However, there are no tables created for my custom entities. Any ideas what it could be? Thanks, Jason Ok, so after some more reserach, I've learned that you can't import data into a custom entity. So that's fun. Can anyone recommend another way to import data from an ...

Missing row numbers, HELP !!!!
I am looking at a spreadsheet, but there are no row numbers. I have column A,B,C,...... Where are my row numbers ???? Tony, Suppression of Row and Column headers is a single option on my Excel have you dragged a window too far to the left? Can you see the left edge of the current worksheet window, or try t maximise the window? Tony Wrote: > I am looking at a spreadsheet, but there are no row numbers. > I have column A,B,C,...... Where are my row numbers ??? -- Bryan Hesse ----------------------------------------------------------------------- Bryan Hessey's Profile: http...

Using Cell Data s Sheet Reference
I would like to have sheets named after a certain text string in a cell and refernce them in formulas For example: I have a sheet named '123Main' and a sheet named 'Main' On the 'Main Sheet' column 'B' is labelled 'Address' Cell B5 has text data '123Main' I want to be able to reference from Main!C5 a formula as such: ="whatever text is in cell Main!B5 is the sheetname to reference a cell on that sheet" I hope it is clear what I'm trying to do.......thanks. -- StompS Portland, OR http://www.geocities.com/pdxinvestr/Stomps.htm...

Data lost during between priv and pub
We work with Exchange 5.5 (2003 is planned for Q2). I have now for the second time encountered a problem with transporting items between the users mailbox and public folders. Rules move mails from the inbox to the right public floder. Apparently this does not allways work correctly. Sometimes the mails cannot be find nor in the inbox, nor in deleted items of the mailbox, nor in the public folders. Is this a known issue with Exchange 5.5? And more inportant; what are my possibilitys to recover the lost mail? ...