Counting numeric but not Alpha

I have three columns and two rows (to make this easy).  The first row of 
cells are blank.  The second row uses a VLOOKUP that pulls a number based on 
a name entered into the first row.  The first two columns have a name but the 
third is left blank (example I am looking up only two names at the time and 
not three).  I want to add the numbers in the second row but if there is no 
name in the first row the second row shows as #N/A because the VLOOKUP is 
pulling no data at the moment.  I want to sum up the numbers pulled by the 
VLOOKUP but the formula I have also counts the #N/A which I want it to ignore.

Any suggestions?  Let me thank you ahead of time.
0
Utf
5/10/2010 7:00:03 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
756 Views

Similar Articles

[PageSpeed] 20

Wrap your VLOOKUP() formula in a 'error trap' that returns 0 instead of #N/A. 
 As:

=IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...))


"Timothy Millar" wrote:

> I have three columns and two rows (to make this easy).  The first row of 
> cells are blank.  The second row uses a VLOOKUP that pulls a number based on 
> a name entered into the first row.  The first two columns have a name but the 
> third is left blank (example I am looking up only two names at the time and 
> not three).  I want to add the numbers in the second row but if there is no 
> name in the first row the second row shows as #N/A because the VLOOKUP is 
> pulling no data at the moment.  I want to sum up the numbers pulled by the 
> VLOOKUP but the formula I have also counts the #N/A which I want it to ignore.
> 
> Any suggestions?  Let me thank you ahead of time.
0
Utf
5/10/2010 7:13:01 PM
Change your VLOOKUP formulas to trap for #N/A

=IF(ISNA(VLOOKUP(G1,$A$1:$F$31,2,FALSE)),"",VLOOKUP(G1,$A$1:$F$31,2,FALSE))

Excel's SUM ignores the "" returned from the ISNA trap.


Gord Dibben  MS Excel MVP

On Mon, 10 May 2010 12:00:03 -0700, Timothy Millar
<TimothyMillar@discussions.microsoft.com> wrote:

>I have three columns and two rows (to make this easy).  The first row of 
>cells are blank.  The second row uses a VLOOKUP that pulls a number based on 
>a name entered into the first row.  The first two columns have a name but the 
>third is left blank (example I am looking up only two names at the time and 
>not three).  I want to add the numbers in the second row but if there is no 
>name in the first row the second row shows as #N/A because the VLOOKUP is 
>pulling no data at the moment.  I want to sum up the numbers pulled by the 
>VLOOKUP but the formula I have also counts the #N/A which I want it to ignore.
>
>Any suggestions?  Let me thank you ahead of time.

0
Gord
5/10/2010 8:02:32 PM
Reply:

Similar Artilces:

Counting # of values in a group registration
I have a small DB that keeps track of community events, where people can enroll and attend various courses. Each course is entered into the DB as well as each enrolled individual, with name, age, etc, but most importantly, the individual's Province. For example, Course DF102 - Gardening, has 20 people enrolled, with each person having the same or different province (BC, AB, ONT, MB, etc). What I would like to do is be able to generate a query that lists each course, and within each course, identify (count) the number of individuals from BC, the # from AB, etc, etc, in orde...

Count Vaules in a range that appear in another range
I have three ranges e.g. Range 1 Range 2 Range 3 Bread Apples Bread Apples Oranges Buns Oranges Pears Buns Bread Pears Apples I'm trying to construct a formula that counts the number of times, say, and entry in Range 3 appears in Range 1 - In this instance the answer would be 3. Hi, try =SUMPRODUCT(($A$2:$A$100=c2)+($A$2:$A$100=c3)) Being a2 to a100 range 1 and c2 and c3 products in range 3 "T Newbery" wrote: > I have three ranges e.g. > > Range 1 Range 2 Range 3 > Bread Appl...

Count cells based on date range in another column
Could someone please suggest how to use a worksheet function to count the number of non-empty cells in one column when the value in another (date) column is in a particular date range? The first column happens to be dates as well, but the formula would ideally work regardless of the counted column's data type. e.g. Given data such as: Column 1 Column 2 --------- -------- 23-Oct-03 2-Jan-04 12-Dec-03 1-Jan-05 14-Feb-05 31-Oct-02 12-Dec-03 1-Jan-05 14-Feb-05 31-Oct-02 12-Dec-03 How do I count the non-empty cells in Column 1 when Column 2 contains a date in the year 2003?...

COUNT unique letter in a column
Howdy All, I want to count the number of occurrences of a partipular letter in a column Any help? THanks, Brian This will give you the number of lower case a's in a range: =SUMPRODUCT(LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100,"a","")))/len("a") If you want to ignore case, you can use: =SUMPRODUCT(LEN(A1:A100) -LEN(SUBSTITUTE(upper(A1:A100),upper("a"),"")))/len("a") (Substitute is case-sensitive) Adjust the ranges to match--but you can't use whole columns (except in xl2007+). Brian wrote: &...

Cycle Count
Hi, Client running GP 10. I am setting up their cycle count schedules and have run into an issue that I can't get an answer for. Want to set up the count to give me the following quantity of items to count weekly: A - 15 Items B - 10 Items C - 5 Items I am unable to find a way to automate this. Any suggestions (besides buying other count software?). -- Jim Lines Sr. Microsoft Dynamics GP Applications Consultant Certified Microsoft Dynamics GP Specialist I don't think so Jim. The assumption behind cycle counting is that you'll count all your inventory at least once annual...

count data in column
Hi, I am using excel97 and trying to create a chart that has 5 columns of data in it a,b,c,d,e. I an trying to make a chart only for certain data in column a and column d. The data that I key off of is in column d and begins with s/ how can I count the number of s/ in column d? how can I create a chart that shows both and only that data that begins with s/ and the data in column a? --- Message posted from http://www.ExcelForum.com/ In cell F2 (I assume row 1 has headers) enter this formula: =LEFT(D2,2) and fill it down as far as you need. select any cell in the table, and apply an au...

Stcok Count Import From Excel
I was just curious if there was any utility that could do stock count export/import from Excel? I was surprised not to see it under IM, I just kind of assumed it would be there. Currently, My plan is to do a refreshable Excel out and a table import in, but that just seems ugly and ripe for mistakes on the inbound side. (We have IM available here, but not Scribe. Otherwise, I'd got that route.) Anyone have any better suggestions? The business purpose here is there are 40 locations and most only have a cash register and inventory case. So, we don't want to put a PC in every lo...

Counting the number cells between two dates
Hi guys, Hope someone can help with this, I'm pretty sure it'll be quite a simple one. Column A:A contains a list dates, I want to use a formula to count the number of cells which contain a date between 01/01/05 - 31/01/05. Any ideas, Many thanks, Dave Try: =SUMPRODUCT((A1:A1000>=--"1/1/05")*(A1:A1000<=-- "1/31/05")) BTW - I'm using American date formats in mine. HTH Jason Atlanta, GA >-----Original Message----- >Hi guys, > >Hope someone can help with this, I'm pretty sure it'll be quite a simple one. > >Column A:A con...

Count Text Data
Using 2007 on Vista If I've got text data which in some columns either has data or there is a blank, what formula do I use to count how many cells have text in them per column? Many thanks =COUNTA(A2:A200) will count everything except blanks post back if you have numbers as well that should not be counted -- Regards, Peo Sjoblom <weewillie@anon.com> wrote in message news:lfm764d39m4prld1iiqllpjuen7a1eptoq@4ax.com... > Using 2007 on Vista > > If I've got text data which in some columns either has data or there > is a blank, what formula do I use to count...

Counting Blank Cells in Pivot Table
How do I have a Pivot Table count the blank cells as well as the other cells? The Pivot table I created counts everything that had data (A, B, C, D, etc...) but does not count the blank cells. Thank you, Jack As answered in microsoft.public.excel.charting: To count the blanks you could use a space character, or the formula ="", in the source data, instead of leaving the cells blank. The pivot table will be able to count those. Jack wrote: > How do I have a Pivot Table count the blank cells as well > as the other cells? The Pivot table I created counts > everythi...

Count if based on 2 criteria
I am attempting to summarize some data based on the values in 2 different cells. Example Count the number of rows where column A = xyz and column U = "this is a test" I know the countif statement can't do multiple criteria, but is it possible to use nested countif statements, or use some combination of AND or IF statements? Thank you Answered in microsoft.public.excel.worksheetfunctions. Please do not post the same question separately to multiple newsgroups. It fragments the thread, and leads to people wasting time constructing answers to questions that have already be...

counting the number of cell formats in a workbook
I'm currently facing the EXCEL error "number of different cell formats too high" (it is a raw translation of french text, so maybe it's not the same message in english) I know that the maximum number of different cell formats is about 4000 per workbook. So my point is : How to trace the evolution of the current number of different cell formats in my active workbook? Which property of which object will give me this figure? Thank you J�r�me -- jérome Yacc ------------------------------------------------------------------------ jérome Yacc's Profile: http:/...

Counting Frequencies of an occurrence
After a join, database has numerous occurrences of a number. I would like to count the number of occurrences of each number in a frequency table and if there were no matching numbers, ie a field is null (how many times does that occur.) Thank you SELECT YourField, Count(*) as FreqCount FROM YourTable GROUP BY YourField -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. <bccdaniel@gmail.com> wrote in message news:1180019296.420091.252510@m36g2000hse.googlegroups.com... > After a join, databa...

Junk email counts
My junk email counter is reporting a larger # than I really have. Are those extra messages hidden somehow or is the counter reporting an erroneous #? Is it set to show total messages or unread count? If you empty the junk folder, is the count correct? -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EA...

count query and negative numbers question
Hi All, Pretty new to database queries so apologise if I sound dumb! I have a database for stock control of energy efficient light bulbs for a charity that gives the bulbs out free to folks in need. The database has various fields including type of bulb, location, quantity and whether the stock has been added to stock or removed. I want to know how many of the different types of bulbs are in stock at any time. I have tried to run a query which makes the 'quantity' amount of bulbs removed to be negative (puts a '-' in front of number). I then want to run a second q...

Counting blanks as zeros
Column AZ contains zeroes as well as blank cells (meaning no value has been entered in the cell). In my formula below, I want to reference only the cells that contain zero and ignore the cells that are blank. As written, the formula is referencing both zero and blak cells. How can I modify the formula to do ignore the blank cells in column AZ? {=SUM(IF(Chart1!$A$2:$A$10000=A3,IF(Chart1!$C$2:$C$10000=B3,IF(Chart1!$AZ$2:$AZ$10000=0,Chart1!$F$2:$F$10000),)))} Thanks, Bob You can add one more condition Chart1!$AZ$2:$AZ$10000<>"" or use SUMPRODUCT() formula as...

sumproduct--counting--zero--blank cells
I'm using these formula to count, =SUMPRODUCT(($W$9:$W$272>=0)*($W$9:$W$272<10)) =SUMPRODUCT(($W$9:$W$272>=10)*($W$9:$W$272<20)) ........etc how do i get it so bank cells are excluded from the count. The way it is now, they are counted in the 0 to 10 range... Thanks Jeremy -- Message posted via http://www.officekb.com COUNTBLANK(range) "jeremy via OfficeKB.com" wrote: > I'm using these formula to count, > > =SUMPRODUCT(($W$9:$W$272>=0)*($W$9:$W$272<10)) > =SUMPRODUCT(($W$9:$W$272>=10)*($W$9:$W$272<20)) > ........etc > how do...

Filtered nodes with template/param, but xsl:number still counts them?
Hi, I posted this today on the xsl-list, but no takers so I thought I'd try here. If nothing else, I would appreciate someone pointing me in the right direction. The following is just a portion of a longer template. I have varying levels of <step> elements with potential customer and assembly attributes. I select the <step>s I want (using the xsl:if and 2 params I declare earlier), then need to create the variable "id" and number the steps in my table -- applying numbers ONLY to those <step>s selected with the IF test. With the following structure, the number...

Employee Count #2
Hello all, we have 500 lic for employee count and we are at 462 right now.. I know we have to order from our supplier but does anyone know where I can find the breakdown on prices to up our employee count...Many thanx... I looked on customer source but could not find... we are still using GP 8 sp 3 don't think that would make a difference but what the hell just through it in there Brian, The pricing is different depending on what licensing mode you are on for GP. Your GP partner is the best source of this information, since they have all your details. -- Victoria Yudin Dynamics ...

Count records in update query
How can I count the number of records which have been updated by my UPDATE query? I tried using DCount but it said you can't use an action query as an argument. Robert Robert wrote: > How can I count the number of records which have been updated by my UPDATE > query? I tried using DCount but it said you can't use an action query as an > argument. > > Robert > > > I would suggest that you add a column to your table that records the time the record was last updated. Then run a query looking for records updated in the last few seconds (depending ...

can Linq extract the largest numeric part from this string list?
Here is the list and the Linq I am using to generate it: ADT.JPG ADT1.JPG ADT10.GIF ADT2.JPG ADT3.JPG ADT4.JPG ADT5.BMP ADT6.JPG ADT7.BMP ADT8.JPG ADT8A.JPG ADT9.GIF var names = from n in files where n.Contains("ADT") select Path.GetFileName(n); foreach (string s in names) Console.WriteLine(s); the number I am looking for is "10" from C:\1C\20CC\PICTURES\ADT10.GIF I am looking for the largest numeric part of a filename. My actual list will also contain strings like this: ADT83A.JPG where I would want to extract 83 from ADT83A.J...

Counting Question #3
I have three columns: dates, values, and names 10/17 $300 Jim 10/17 $300 Jim 10/17 $200 Tom 10/17 $100 Jim When I enter Jim and $300 in to two separate cells, in a third cell I want to count the number of "days" it applies to (all the way down the spreadsheet.) So in other words, there are two instances in the same day of Jim and $300, but since it all happened on one day, the answer would be one. Hope I explained it well. =sumproduct(--(b1:b10=x1),--(c1:c10=x2)) where x1 holds the amount, x2 holds the name and B1:B10 holds the list of amounts and c1:c10 holds the list of...

count between dates
I have dates in column A and names in column B. I need to count how many names there are say between 1 Jan & 8 Jan. Help before my brain explodes.... Here's how I'd do it, Vicki: Create a helper column, say in N, "=AND(A2>=DATE(2010,1,1),A2<=DATE(2010,1,8))". Then use =COUNTIF(N:N,TRUE) and it'll count how many rows have dates in that range. Do you need to eliminate duplicate names? --- "Vicki Leibowitz" wrote: > I have dates in column A and names in column B. I need to count how many > names there are say between 1 Jan & 8...

Converting Numerical values to Words
I am fairly new to the ins and outs of Microsoft Access 2003 but have been able to work through all of my problems thus far except one. I started using check boxes for storing user inputed data from a form. When the box is checked I have made it equal a value ranging from 1 to 6 according to the desired group. This stores the number in a table which I then reference many times in queries as well as reports. Here is my question, how do I convert from those stored numbers, taken from the check boxes, to words that can be easily outputted to a report so that anyone can read it without ...

count users on database / store script
Greetings. I work for a large enterprise with several exchange servers, each server has 2 storage groups, and each storage group has 3 databases. I need a script that will produce a CSV with total number of mailboxes on each database. I eventually will post this information on our intranet using a data binding table. Can anyone help get me started on producing such a script? Thanks In Advance. Rich Nahra Via Exchange System Manager - Expand mailbox store - mailboxes - Right click - Export List - Open with Excel and scroll to the bottom for the total - subtract one for the header. ...