Use of '=AND( COUNTIF(A:A, B1))' to check if a cell is in a column or range

Hello.

Can someone please help, I'm using Excel 2003 and I wondered if this is
an efficient way of checking whether an item is is a column or part of
a column of (length 5000 rows) or not: -
=AND( COUNTIF(A:A, B1) )

It seems like an odd use of the AND function as it only has one
parameter but it returns a TRUE or FALSE as you would expect.

I don't want to count the number of occurences, I just want to know if
the value is present in the column but I can't seem to find an
alternative to COUNTIF.

The IF and FIND commands don't seem to apply here.

Thanks for any help given.
Hal

0
sillyhat (3)
5/23/2006 5:53:06 PM
excel 39879 articles. 2 followers. Follow

2 Replies
724 Views

Similar Articles

[PageSpeed] 0

=COUNTIF(A:A,B1) will do!
The AND function is extraneous.

HTH,
--
AP

<sillyhat@yahoo.com> a �crit dans le message de news: 
1148406786.188621.306810@j73g2000cwa.googlegroups.com...
> Hello.
>
> Can someone please help, I'm using Excel 2003 and I wondered if this is
> an efficient way of checking whether an item is is a column or part of
> a column of (length 5000 rows) or not: -
> =AND( COUNTIF(A:A, B1) )
>
> It seems like an odd use of the AND function as it only has one
> parameter but it returns a TRUE or FALSE as you would expect.
>
> I don't want to count the number of occurences, I just want to know if
> the value is present in the column but I can't seem to find an
> alternative to COUNTIF.
>
> The IF and FIND commands don't seem to apply here.
>
> Thanks for any help given.
> Hal
> 


0
ardus.petus (319)
5/23/2006 6:10:43 PM
The AND is used to force a Boolean result, otherwise you get 0,1,2,etc.

You could do the same with

=COUNTIF(A:A,B1)>0

-- 
 HTH

Bob Phillips

(remove xxx from email address if mailing direct)

<sillyhat@yahoo.com> wrote in message
news:1148406786.188621.306810@j73g2000cwa.googlegroups.com...
> Hello.
>
> Can someone please help, I'm using Excel 2003 and I wondered if this is
> an efficient way of checking whether an item is is a column or part of
> a column of (length 5000 rows) or not: -
> =AND( COUNTIF(A:A, B1) )
>
> It seems like an odd use of the AND function as it only has one
> parameter but it returns a TRUE or FALSE as you would expect.
>
> I don't want to count the number of occurences, I just want to know if
> the value is present in the column but I can't seem to find an
> alternative to COUNTIF.
>
> The IF and FIND commands don't seem to apply here.
>
> Thanks for any help given.
> Hal
>


0
5/23/2006 7:20:21 PM
Reply:

Similar Artilces:

run report that uses a set of dates #2
Once again I am baffled! I need to keep track of projects that get checked out to be updated. Each sheet is a different project. In column B I have when the project was checked out. IN column C I have the date the project was checked back in. The formulas I am currently using are: Column D: =TRIM(IF(B9<>"","out","")&" "&IF(C9<>"","in","")) which gives :"out" if not checked in and "Out IN" if it is. Column E: =IF(B9>C9,"OUT","In") This tells me if the project...

Charting cells that have been grouped
When charting a large range of cells, which have been grouped in sets of 12 for convenience and easy review, the chart using this data is blank. If I ungroup the cells, the chart shows the data. Is there a way around this? Why can't a chart show the data even if the data is hidden? I don't know what you mean by "grouped" cells. Are you hiding cells? By default Excel doesn't display hidden data on a chart. To change this behavior: Select the chart, go to Tools menu > Options > Chart, and uncheck Plot Visible Cells Only. - Jon ------- Jon Peltier, Microsoft...

nesting functions to compare segments of two columns
In Excel I would like to nest functions to make the formula =MAX(OFFSET((ADDRESS((MATCH(I1,F1:F8000)),6)):(ADDRESS((MATCH(I2,F1:F8000)),6)),0,1)) The part =ADDRESS((MATCH(I1,F1:F8000)),6) works on its own as does =MAX(OFFSET(F14:F23,0,1)) but they don’t work together. The intent is to be able to type in two numbers; a minimum value (I1) and a maximum value (I2). The location of those values would then be identified in a column of ascending numbers (F) but the numbers would make a range separated by a variable number of other cells dependant on the min & max inputs. Th...

Date range in x axis
I am using a simple chart to plot a range of values against weekly dates (Monday 5/01/2009 to Monday 28/12/2009) Excel inserts 3 dates before and after my date range - how can I force the graph to only display the dates within the data range? I have just worked out how to do it - so in case anyone else is having similar problem - In the Axis options change the Minimum and Maximum to Fixed and change the number (the sequencial date conversion number) by adding 21 to the minimum and taking 21 off at the maximum values. This has the effect of moving the start day forward by 21 days and ...

In Excel I need to insert a clean form each use
I have created a protected worksheet with unlocked cells to be filled in by the user and saved. Then next week I need to to have another copy of the same blank form for the user to fill in with new data. I thought I could just insert a copy of the form (as its own file) on Sheet 1, but can't figure out how to do that. Robin, Hold down the CTRL key, left click the sheet tab and drag to create a copy. It will have a name of something like Sheet1 (2) so rename as required. Mike "Robin" wrote: > I have created a protected worksheet with unlocked cells to ...

How do I lock a group of cells in Excel?
I'm trying to lock down a group of cells so they won't be able to move. How do I do this? - Tools / Protection / Protect Sheet - Remember to unlock the cells that you want to be able to put input into. - Format / Cells / Protection - uncheck the 'Locked' checkbox HTH, Gary Brown "Ben Nesbitt" wrote: > I'm trying to lock down a group of cells so they won't be able to move. How > do I do this? ...

Scroll bars in Cell
Hi all, It seems like you can only size a cell so that it is 409.50 pixels. I have large ammount of text that I would like to put into a cell, and be able to scroll through that text. It would keep the size of the cell minimum, and allow the user to easily see what are the contents of the cell. Is this possible in Excel 2000? Is there a similar way to do this? Thanks for your help, Stephen Hi Stephen This is not possible for a cell (that is attach scrollbars to it) -- Regards Frank Kabel Frankfurt, Germany Stephen Closson wrote: > Hi all, > > It seems like you can only size a ...

automatic column.autofit
I have a csv text file that I want to automatically open with column autofit. the text file is generated from a query to a remote database, so it cannot contain any code. pls advise mechanism. Igor CSV files are just plain ascii text files. They'll never contain code. So you could have a wrapper workbook that opens the .csv and does your formatting or you could do it manually. Igor Lisbaron wrote: > > I have a csv text file that I want to automatically open with column > autofit. > the text file is generated from a query to a remote database, so it cannot > contain ...

using silent setup with Microsoft Custom Installation Wizard
I created a custom installation MST script to deploy Outlook 2003 within my company. So far I've been using executing it manually having users click on a batch file but I'd like to deploy it through the login script. I want to do a silent setup, or at the very least disable the cancel option to keep users from stopping the installation. The command line I'm using to execute the install goes like this: setup.exe TRANSFORMS=\\SERVER\OUTLOOK2003\OUTLOOK-INSTALL.MST /qb- Can I modify the switch at the end in order to prevent users from cancelling the script? Alan, I am not sur...

Pivot Table Refresh Changes Color Scheme for Some Cells
Office 2007 XP SP3 I want to display a Pivot Table output in a certain (blue) format. When I refresh a Pivot Table, the color scheme on some cells changes, in my case from blue to orange. Any ideas? Your help is greatly appreciated. 8^> ...

Repeating Cell References in a formula
I'm a relative newby so this may be an easy question. I am trying to use the OR function to reference several occurances suc as: =if(or(a1="KY",a1="NY",a1="WY",a1="TX",a1="VT"),"Valid State","Invali State")) I need to way down with this formula since the various states will sho up many times. Also I've shown only 5 states in the formula bu actually have many more. My question is: Is there a shortcut to typing "a1=...." over an over? Hope this makes sense and thanks in advance if you can help -- Mess...

checking whether the current object is a specific class.
Hello. I need to check whether the current object Is a specific class, For example : function a(b as object) ' I need to check here, whether b is Button class. ' How can I do that ? end function Thanks :) Am 10.06.2010 21:36, schrieb Mr. X.: > Hello. > I need to check whether the current object Is a specific class, > > For example : > function a(b as object) > ' I need to check here, whether b is Button class. > ' How can I do that ? > end function > > Thanks :) What's your intention? -- Armin On J...

Portfolio Total Change Column
Wonder if this is a bug. In the Total Change Column, the numbers are not correct. For example, I have several stocks that are down today but the column is showing positive values. The grand total is also showing a positive value although it should be negative. Anybody else having this problem? Greg ...

I make to write in a cell list validation?
I have a cell is ready of data but sometimes nees to write thing and not to use the smooth one sometimes and if but the book to use every month clean it and I return it to use. Since I make to write in a cell list validation? Carlos Hi Carlos I'm not very sure I understand what you want, but I'll give it a shot: For a custom list, go Data > Validation, "Allow: List" and in "Source" write something like Carlos,Harald,Jenny,Maria (use semicolons instead of commas if those are your regional list delimiters). To permit other entries than your list conte...

Summing up specific cell references and its correlating value
I want to get a total sum of "A" without having to add up eac individual "A" value. Example: January A, "25" (b1) B, 24 D, 15 February A, "30" (b8) C, 20 B, 5 April B 50 C 35 My formula for A's total sum now reads: =$b$1+$b$8 The "$" signs are there because periodically the data in that specifi month is sorted by descending amounts. Is there a "sumif" formula that can find all the "A's" and will sum u all values in the adjacent cell? Thanks in advance. - -- Message posted from http://www.ExcelForum.com H...

convert text column to date.
Hello all, I have a column on a table with the following text data: Feb 24 Mar 7 Apr 3 .... etc I want to create a query to read this column an write it to a date column in the format dd/mm/yyyy Any suggestion? Thank you in advance On Wed, 19 Sep 2007 16:02:03 -0700, Miguel Alvarez <Miguel Alvarez@discussions.microsoft.com> wrote: >Hello all, >I have a column on a table with the following text data: >Feb 24 >Mar 7 >Apr 3 >... >etc >I want to create a query to read this column an write it to a date column in >the format dd/mm/yyyy >Any suggestion? >Tha...

"Not plotted" option / blank cells in graph
I have a similar problem to "blank cells in graph" already posted by Kirsty, ie. 'Plot empty cells as: Not plotted' option is greyed out. The cells are empty, (no formula). My intention is to extend the formula from week to week (every time the chart will be updated. I'm using a Stacked Area chart - maybe that's the problem? ie. it's not possible to have a Stack Area chart to "stop"? Thanks in advance.... ...

reference format and column name
I have 2 problems, likely, related to some reference format in Excel 2003: 1) Column names are displayed in numbers, like rows; 2) When I type a reference to a cell, the formula contains letters R and C and number of cells, the referred cell is away from the cell with the formula (instead of a regular number of column and row). I don't know how I got to this format. Please get me back to columns named in letters, and regular type of displaying references. Many thanks. hi on the menu bar>tools>options>general tab uncheck R1C1 reference (upper left) Regards FSt1 ...

How do I Un-merge two columns half way down?
I am creating a name & address list for a club. Half way down two columns (F and G) have merged. I cannot get it back into two separate columns again. Can any-one advise me, please? I am using Excel from Office 2000. Try selecting the col headers F and G Then click Format > Cells > Alignment tab Click* as required to uncheck "Merge cells" completely > OK *you may probably need to click twice -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Hattie" wrote: > I am creating a name & address list for a club. Half way down two column...

Occurances of items in a range.
Hello all, I have a large field of data about 17,000 rows. What I want to do it break it into increments of 500 and then have excel count the occurances of each increment and in another cell total it. Please, for purposes of your responses assume all original data is in sheet 1 Column "F" I want the ranges to look like this: -5000 to -4501 -4500 to -4001 -4000 to -3501 ............... 0 to 500 501 to 1000 ............... etc all the way to 4500 to 5000 Is what I'm asking even possible? and if so any help would be greatly appreciated. Thank you, Shhhh "Shhhh&quo...

I need tutorials to use outlook
Please address me where I may be able to find instrucction on how to use the outlook as a begginer? http://office.microsoft.com/en-us/outlook/FX100647191033.aspx -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, Yol wrote: | Please address me where I may be able to find instrucction on how to | use the outlook as a begginer? "Yol" wrote in message news:72140F56-9662-439A-B638-994C7BD66FA0@microsoft.com... > Please...

Help Using a Combination Function
BACKGROUND: I'm trying to create a spreadsheet that would help me do the monthly schedule for our nurses more efficiently. Right now, the spreadsheet is just fully manual, add all the dates (to six sheets), review the schedules cell by cell to make sure we have enough people scheduled and then take the daily schedules and apply to yet another spreadsheet that shows the daily schedule (versus the 28 day schedule). CURRENTLY: I have created a new spreadsheet that has already automated some of these functions. I have created a cell so that I only have to enter one date and ...

Database backup whilst the POS is in use
I currently do my daily database backup during the night using a scheduled Windows task. The backup is copied to multiple hard disks on different PCs. I would like to do additional backups during the day whilst the POS is being used. Does RMS SO actually allow this or would I have to briefly shutdown the POS for the backup? I am on V2, SP2. Thanks for your reply. Yes, you can run backups while POS is running. You can even run backups when multiple manager programs are running. SQL server is built to handle many different concurrent connections. As another option, you can also use the c...

Is it possible to use CRecordSet without connecting to a database?
Under .NET you can use the ADO.net object without never connecting to a database. Creating columns and inserting data manually. Is it possible to do so with a CRecordSet? If it is important to know, I need to do so because I have a working C++ function that accepts a CRecordSet derived class as a parameter (ugly, I know), I'd like to use the function and send it a RecordSet, just like it used to get before, however, all that without connecting to a database. - I'll fill it myself using another source. Is it possible? Thanks, Vitaly "Vitaly Belman" <vitalyb@gmail.com>...

How do I copy a comment to become the information in a cell.
I am trying to copy comments in a spreadsheet into cells on the same line so that they become the cell not a comment attached to a cell. The macro below inserts the text from a comment in the activecell into the first blank cell in the row. The comment is then deleted so save your work before testing this: Sub cmt() Dim eCol As Integer Dim cmtText As String Dim cmt As Comment Set cmt = ActiveCell.Comment If Not cmt Is Nothing Then With ActiveCell eCol = Cells(.Row, Columns.Count).End(xlToLeft).Column + 1 cmtText = Application.WorksheetFu...