If and blanks

Is using ifnumber the best way to have a formula not count blank cells
as zeros?
If so, can someone show me using =COUNT(C3:C31)


-- 
LucasBuck
------------------------------------------------------------------------
LucasBuck's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19710
View this thread: http://www.excelforum.com/showthread.php?threadid=467238

0
9/13/2005 6:34:38 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
332 Views

Similar Articles

[PageSpeed] 48

Without you telling us what data (or blanks or zeros) are in your
specified range, we can't understand what you are looking for.  COUNT,
by definition, only counts cells containing numbers (from the HELP
file: -Counts the number of cells that contain numbers -).

Your formula, as written should do what you ask.  Also, I am not
familiar with "ifnumber".  Is that a UDF?


-- 
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15101
View this thread: http://www.excelforum.com/showthread.php?threadid=467238

0
9/13/2005 6:44:04 PM
Sorry, I'll be more specifc (the ifnumber was just something I saw while
searching)

I I did get part of it worked out. I had asked earlier how to count
pairs of zeros in a list with 2 columns (in otherwords, two zeroes in
the same row) Someone suggested
=SUMPRODUCT(--(B1:B31=0),--(C1:C31=0))
The problem is, it counts blanks as zeroes. Everything is set up so
items can be added later, so it's going to have blanks unless I adjust
it every time.


-- 
LucasBuck
------------------------------------------------------------------------
LucasBuck's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19710
View this thread: http://www.excelforum.com/showthread.php?threadid=467238

0
9/13/2005 7:36:57 PM
Read my reply in your previous post, I specifically addressed that issue.

-- 
 HTH

Bob Phillips

"LucasBuck" <LucasBuck.1vbcue_1126641909.832@excelforum-nospam.com> wrote in
message news:LucasBuck.1vbcue_1126641909.832@excelforum-nospam.com...
>
> Sorry, I'll be more specifc (the ifnumber was just something I saw while
> searching)
>
> I I did get part of it worked out. I had asked earlier how to count
> pairs of zeros in a list with 2 columns (in otherwords, two zeroes in
> the same row) Someone suggested
> =SUMPRODUCT(--(B1:B31=0),--(C1:C31=0))
> The problem is, it counts blanks as zeroes. Everything is set up so
> items can be added later, so it's going to have blanks unless I adjust
> it every time.
>
>
> -- 
> LucasBuck
> ------------------------------------------------------------------------
> LucasBuck's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=19710
> View this thread: http://www.excelforum.com/showthread.php?threadid=467238
>


0
bob.phillips1 (6510)
9/13/2005 8:17:36 PM
simply add one more condition to the formula, as such:

=SUMPRODUCT(--(B1:B31=0),--(C1:C31=0),--(B1:B31<>""))

HTH

Bruce


-- 
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15101
View this thread: http://www.excelforum.com/showthread.php?threadid=467238

0
9/13/2005 8:20:51 PM
Hi Lucas

I was one of the people who gave you that solution, but I did not consider 
the case of blank cells.
Bob Phillips had thought about the problem more deeply, and also posted a 
solution with another condition added which addressed the value of blank cells.

I case you missed his posting
=SUMPRODUCT(--(A1:A10=0),--(B1:B10=0),--(A1:A10<>""))

or for your ranges
=SUMPRODUCT(--(B1:B31=0),--(C1:C31=0),--(B1:B31<>""))


Regards

Roger Govier


LucasBuck wrote:
> Sorry, I'll be more specifc (the ifnumber was just something I saw while
> searching)
> 
> I I did get part of it worked out. I had asked earlier how to count
> pairs of zeros in a list with 2 columns (in otherwords, two zeroes in
> the same row) Someone suggested
> =SUMPRODUCT(--(B1:B31=0),--(C1:C31=0))
> The problem is, it counts blanks as zeroes. Everything is set up so
> items can be added later, so it's going to have blanks unless I adjust
> it every time.
> 
> 
0
roger1272 (620)
9/13/2005 8:58:15 PM
Thank you both very much. I didn't notice the addition in the othe
post. Very much appriciated. (Trying to finish something for you
principal when you have 29 kids in the room can lead to distraction.
:

--
LucasBuc
-----------------------------------------------------------------------
LucasBuck's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1971
View this thread: http://www.excelforum.com/showthread.php?threadid=46723

0
9/13/2005 9:24:28 PM
Reply:

Similar Artilces:

Delete blank rows Macro
Using MS Excel 2000. Help creating a macro please. I need to search the range A1:A10000 and if the cell is blank I want to delete the entire row. Thanks in advance. -- Richard "Richard" <Richard@discussions.microsoft.com> wrote in message news:DD28F95E-58CF-4961-A3AD-E37FC68AE0F4@microsoft.com... > Using MS Excel 2000. > Help creating a macro please. > > I need to search the range A1:A10000 and if the cell is blank I want to > delete the entire row. > > Thanks in advance. > -- > Richard Something like Sub DeleteRowsIfBlank() Dim lrow as Long...

Report = Blank?
Hello, I have a report that I got from this forum, and when I use it, everything is fine. The problem I am currently having though is if say I try and generate the report using RegisterID & Date then I get a blank report...if I try one or the other, then I am fine...I pretty much tried every angle with RegisterID and with Date, and nothing...any advise would be great, as I have seen this happen with other reports as well, where the final outcome is a blank report. Thank You, Vince "When I run into problems, we more often than not fix them ourselves. Can't put a price on ...

Deleting Blank Characters in a Cell
I have a list of names with email addresses of attendees of a seminar series that I am trying to compile into a list that can be copied and pasted into an email (using BCC as the methodology for emailing). The challenge that I have is that when these files were exported from the database to excel (not sure how), all of the cells contain exactly 100 characters even though the email address may only contain 13-20 characters. Is there a way to "trim" the blank characters after the last character that is used by a letter instead of blank character field? Thanks in advance! PZan --...

Blank .msg attachments
Here's an odd one, When an email has been forwarded to, or replyed to and is using the 'attach as attachment' option (as opposed to embed text), the message is delivered with attachment, but the attachment appears blank. It is only .msg attachments that have this problem, everything else gets thru fine (.doc, ..xls, .exe etc.) The attachment is 64bytes long, and has nothing in it. Exchange 2003, Windows 2003 and Outlook 2003 being used. Any ideas anyone? -- The man with no shoes. MCSE (W2K3, W2K), MCSA (W2K3, W2K), MCSE+I (NT4), CCNA + others.... All replies to group pleas...

Blank Cells #4
Hello, I am trying to do the following: 1 - In Cell A4 display the SUM of A1 + A2 + A3 if at least one of these cells is not empty. 2 - In Cell A6 display A5-A4 if both cells are not empty. How can I do this? Thank You, Miguel = Sum(A1:A3) ...and... =IF(AND(LEN(A5),LEN(A4)),A5-A4,"") -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware (free and commercial excel programs) "Shapper" <mdmoura@gmail.com> wrote in message news:25110584.2678.1325119728434.JavaMail.geo-discussion-forums@yqnd19... > Hello, > > I am trying to do ...

conditional formatting:highlight row based on blank or non-blank c
Does anyone know if, and how, is possible to use Conditional Formatting feature to automatically highlight the whole row if a specific cell in that row is non-blank (or blank)? Thank you! Hi, Yes, first select your row then in your conditional formatting select "formula is" from the drop down menu and type: =ISBLANK($A1)=FALSE or =ISBLANK($A1)=TRUE depending on weather you want the condition to apply when your cell is blank or non blank. HTH JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum....

Counting With Blank Rows
Hi, If I have a column of numbers like this: 4 5 6 3 7 8 9 3 2 7 4 1 4 How do I count the number of items between the blanks so it will look like I have it in the example. The number to the right and bottom is the count. So, for the numbers 4,5 6 the count is three. For 7,8 9 the count is three and for the numbers 2,7,4,1 the count is four. Is there a way to do this withoug using a macro? Thanks. Hi, You need to use the "count" function. If the rows in your example are 1-12 and the column is A, for the first group you'd type in (in column B) "=count(a1:a...

Charting blanks as spaces
I know you can chart blank cells as spaces by clicking it in the options...this is a little different. The cell it is charting as an if function in it to display a blank cell. example: =if(D3="","",D5-D3) So when D3 is blank it should display in cell D6 a blank. But when it charts it, it charts it as a zero. I want it to chart it as a gap. I tried =if(D3="",NA(),D5-D3), This way i get a "#N/A" error in my cell, which i can hide through conditional formating. but when i click display>data labels>values in chart options it displays "#...

Blank, yet not blank
Hi, I copied some info from a database and pasted it to Excel where I wanted to manipulate the data. I notice that some of the cells, though appearing to be blank (and not showing 0 either), still responded to a simple formula like IF(OR(A1="",A1=0),1,0) giving the answer as 1. Can anyone tell me what's happening here, as it does mean I need to unneccessarily extend the formulas to exclude these cells. Rob "Rob" wrote: > Hi, > > I copied some info from a database and pasted it to Excel where I wanted to > manipulate the data. I notice that so...

BLANK FORM / QUERY
My database is pretty basic with tables, forms, queries etc. I'd like to display a form that allows me to type in a figure in the first field and the rest of the fields will populate automatically. I'm not sure how to create a query where the field awaits input. Thanks in advance. -- TomMurray In the query's criteria box, use square brackets: [Enter a value] or from a form: [Forms]![FormName]![txBoxName] in the first case, you will be prompted. In the second, you will only be prompted if the form isn't open. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http:/...

Blank Records in Catalog Merge
I am printing a directory with the following information: Name Child's Name Phone Number Address The records that do not have an entry under "Child's Name" are coming out like this: Name Phone Number Address (...with a blank space where the "Child's Name" field would be.) Even worse (and more confusing when reading the final document) is when the record has neither a "Child's Name" entry nor a "Phone Number" entry, and you get this: Name Address Basically, I want it to ignore the line if there is no data in the field. Accord...

Blank Document no longer blank
Every time Word opens, words and a picture (that were used at one time) appear as the blank document. I cannot find any place/button/option that will remove the words or picture from the default new document HELP please! See http://word.mvps.org/faqs/apperrors/blankdocnotblank.htm for tips on how to remove content from the Normal.dot(x) Hope this helps DeanH "AnaMarie" wrote: > Every time Word opens, words and a picture (that were used at one time) > appear as the blank document. I cannot find any place/button/option that will > remove the words or p...

Page shows up in print preview, but prints blank...
I'm trying to print a booklet with graphics on both the front and back covers. The back cover graphics and text appear fine in print preview, but when I send it to the printer, it prints blank. The front cover is fine. Help! Thanks in advance to anyone who can help me. How to troubleshoot problems that you may experience when you try to print to a local printer by using Office programs in Windows XP http://support.microsoft.com/kb/870622/en-us Have you checked the manufacturer's web page for your model printer? There maybe an updated driver. -- Mary Sauer MSFT MVP http://offi...

Blank Email
I have a customer with nagging and intermittent Exchange issue. Every so often when they send an email, the body of the email is blank, though there was text when they hit send. The Sent Items folder also shows the email with a blank body. There are no errors on the Exchange Server. It is happening on both the Terminal Server and on a PC, so it looks like it's Exchange Specific. Has anyone seen this? There in nothing on MS, and Google groups asks the question with no answer. Help! -Hal ...

Copy named range, but skip blanks
My goal here is to copy "NewRecord" and paste values only in the first blank cell in column A on the Payment History sheet, while skipping blanks rows. "NewRecord" has 22 rows with formulas in columns A, B & C. Sometimes all 22 rows will have data entered, sometimes only the 1st row. The marco works, but does not skip the blank rows in NewRecord - is it because there are formulas present? How do I fix this? Also, how do I assure my macro is pasting on the first blank row of column A? Application.Goto Reference:="NewRecord" Se...

Blank.potm with vba
I have a template ‘blank.potm’ with a macro ‘btnActionPPt’. With the custom UI Editor I have made a new tab ‘Name’ with a button with a onaction=’btnActionPPT’(XML). If I open the blank.potm, it works perfect. The vba-macro open a new powerpoint-template. However, if I start powerpoint, the tab ‘Name’ is active, but the button give a error that te macro do not found. In te vba-editor there the macro is active. In Word it works correct. Who can help me? Dick In article <AC1773F4-380F-4393-BA76-4EBC7559F06D@microsoft.com>, Dick wrote: > I have a template �blank.po...

Blank Message Bodies in OWA
I'm running Exchange 5.5 SP4 on NT4 SP6. Using OWA end users can successfully access the Inbox but when they open a message the body is blank. If they click on reply or forward, when the form opens it shows the content of the body so it is a weird situation. I was having some problems that were fixed with KB818709, I suspect this problem started after that; however I can't say for sure that this problem started after I applied that fix. I have already applied the fixes recommended in KB314532 but it didn't help. I hope somebody has an idea about what to try next. Thanks. I'd ...

sum of blank and non blank
Hi, How do I count blank cells in a range which contains text and separately how do I count cells that are not blank? I used =countblank(range) which works but how do I do the opposite, count cells WITH text in range? Tx, S =COUNTA(range) will count all cells that are not empty in "range". =COUNTIF(range,"*") will count only cells that contain text. HTH Jason Atlanta, GA >-----Original Message----- >Hi, How do I count blank cells in a range which contains text and separately >how do I count cells that are not blank? >I used =countblank(range) which work...

Blank field problem at access 2007
Hi, I have a query with 3 columns. 1st and 2nd columns are from a table that includes numbers. 3rd column is for summation of 1st and 2nd columns. I have no summation if a field is blank. For example, query gives a result something like below: 1st_______2nd________3rd 10________20_________30 3_________5__________8 Blank______7__________Blank (must be 7 ???) 15________Blank_______Blank (must be 15 ???) How can I solve this problem? Thanks a lot in advance. Use Nz() around each of the numbers. Presumably you have a calculated query field that looks like this: Col3: [Col1] + [Co...

Non Blank
I have a very large spreadsheet that was given to me by someone who created it from a database. I need to do a CountA to help sort it. But the "empty" cells, which look blank to me, are not. So the CountA counts every cell. Would you know how I can get the cells that look empty to be seen as blank so I can do a valid CountA? Here's a solution for you. Step 1, of course, is to make a backup copy of your data so you don't lose anything! Then copy this code, and paste it in as a macro. It stores the value of each cell to memory if the cell does not contain a formula, del...

Blank Vlookup
I want to know if a table has a blank field and I want this blank fiel returned as Tomato, I use Vlookup for all the other results, but if i is blank I need it to be retured as Tomato, any ideas? Colli -- Message posted from http://www.ExcelForum.com Hi maybe something like =IF(A1="","Tomato",VLOOKUP(A1,lookup_range,col_index,0)) -- Regards Frank Kabel Frankfurt, Germany > I want to know if a table has a blank field and I want this blank > field returned as Tomato, I use Vlookup for all the other results, > but if it is blank I need it to be retured as Toma...

Blank Mail Body
We are using Outlook 2003 with SBS 2003 and are having trouble with sending messages. The message on a reply is blank when format is set to html or rtf. We also can not set format to rtf at all as all messages sent will be blank. Anyone have resolution to this issue? All machines are current on updates to windows 2000 and outlook 2003. Thanks, Randy- ...

Blank cell not really blank
In working to eliminate blank cells in a range, it has become apparen that when I do a range/copy/paste special/values although the copyie cells appear blank they reall are not. Is there a better way to copy range of values that will truly leave the blank cells blank. I need t do a count via counta() and of course that will not work because it i seeing something. Thanks, I really appreciate this site. JR -- Message posted from http://www.ExcelForum.com Where are you copying your data from?? -- Regards Ken....................... Microsoft MVP - Excel Sys...

sumup blank cell to blank cell
Can any body think of a formula if i want to sum up each collection as follow, because i have a lot of collection cell i have to calculate. A 5000 B 2000 C 3000 Collection : 8000 D 1000 E 2000 F 4000 Collection 7000 G H I Collection B2 and copy down =IF(A2<>"","",SUM(A2:INDEX($A$1:A1,MAX(1,MAX(IF($A$1:A1="",ROW($A$1:A1))))))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do ...

Checking for a blank Cell
Hi there, I am currently devising a formula within the N column of m spreadsheet. I am using a lookup table to check whether or not th apartments I have are "In Progress" or "Expired". I have managed to d this successfully. However, I wish to create a third option of N/A whe a flat is not occupied (i.e. there isn't a date of expiration in the column). I believe that I shall need a nested IF statement to do a additional check to the current formula I have for the N colum =IF('LOOK-UP'!$B$1>+'18 OCT - 14 NOV 2004'!M2,"EXPIRED","I PROGR...