How to refer to current cell in range

This is a simple problem, but after a couple hours of searching Excel
help and Newgroups, I can't seem to find anything helpful.

In Excel 2000

I've been using the countif function to count cells that contain a
certain phrase, e.g. this counts the cells in the range that contain
the phrase
"W2K Pro":
=COUNTIF(PCs!D2:D143,"W2K Pro")

What I'd like to do is count cells in which the LEN() function returns
a value greater than 12, so something like this:

=COUNTIF(PCs!D2:D143,LEN(currentcell)>12)

I just can't seem to find a way to refer to the current cell when the
range is processed.  Anyone have any ideas?

Thanks,

Mike
..
0
selowan (2)
12/12/2003 5:09:10 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
460 Views

Similar Articles

[PageSpeed] 14

"Mike" <selowan@yahoo.com> wrote in message
news:9a3e5b42.0312120909.2a63bdf1@posting.google.com...
> This is a simple problem, but after a couple hours of searching Excel
> help and Newgroups, I can't seem to find anything helpful.
>
> In Excel 2000
>
> I've been using the countif function to count cells that contain a
> certain phrase, e.g. this counts the cells in the range that contain
> the phrase
> "W2K Pro":
> =COUNTIF(PCs!D2:D143,"W2K Pro")
>
> What I'd like to do is count cells in which the LEN() function returns
> a value greater than 12, so something like this:
>
> =COUNTIF(PCs!D2:D143,LEN(currentcell)>12)
>
> I just can't seem to find a way to refer to the current cell when the
> range is processed.  Anyone have any ideas?
>
> Thanks,
>
> Mike
> .

You can't do this with COUNTIF. COUNTIF (and SUMIF) will only take a cell
range for their comparison. You need a function of a cell range [i.e.
LEN(cell range)]. You can achieve the result you want using SUMPRODUCT:
=SUMPRODUCT((LEN(PCs!D2:D143)>12)*1)


0
Paul
12/12/2003 5:18:39 PM
One way:

    =SUMPRODUCT(--(LEN(PCs!D2:D143)>12))

In article <9a3e5b42.0312120909.2a63bdf1@posting.google.com>,
 selowan@yahoo.com (Mike) wrote:

> This is a simple problem, but after a couple hours of searching Excel
> help and Newgroups, I can't seem to find anything helpful.
> 
> In Excel 2000
> 
> I've been using the countif function to count cells that contain a
> certain phrase, e.g. this counts the cells in the range that contain
> the phrase
> "W2K Pro":
> =COUNTIF(PCs!D2:D143,"W2K Pro")
> 
> What I'd like to do is count cells in which the LEN() function returns
> a value greater than 12, so something like this:
> 
> =COUNTIF(PCs!D2:D143,LEN(currentcell)>12)
> 
> I just can't seem to find a way to refer to the current cell when the
> range is processed.  Anyone have any ideas?
> 
> Thanks,
> 
> Mike
> .
0
jemcgimpsey (6723)
12/13/2003 1:16:25 AM
Thanks for the help, it worked well.  There's no way I would have
guessed from reading the help file, that SUMPRODUCT could be used in
this manner


"J.E. McGimpsey" <jemcgimpsey@mvps.org> wrote in message news:<jemcgimpsey-626752.18162512122003@msnews.microsoft.com>...
> One way:
> 
>     =SUMPRODUCT(--(LEN(PCs!D2:D143)>12))
> 
> In article <9a3e5b42.0312120909.2a63bdf1@posting.google.com>,
>  selowan@yahoo.com (Mike) wrote:
> 
> > This is a simple problem, but after a couple hours of searching Excel
> > help and Newgroups, I can't seem to find anything helpful.
> > 
> > In Excel 2000
> > 
> > I've been using the countif function to count cells that contain a
> > certain phrase, e.g. this counts the cells in the range that contain
> > the phrase
> > "W2K Pro":
> > =COUNTIF(PCs!D2:D143,"W2K Pro")
> > 
> > What I'd like to do is count cells in which the LEN() function returns
> > a value greater than 12, so something like this:
> > 
> > =COUNTIF(PCs!D2:D143,LEN(currentcell)>12)
> > 
> > I just can't seem to find a way to refer to the current cell when the
> > range is processed.  Anyone have any ideas?
> > 
> > Thanks,
> > 
> > Mike
> > .
0
selowan (2)
12/16/2003 9:00:13 PM
Reply:

Similar Artilces:

Sum of every nth cell in a column
I am trying to figure out the formula for adding the nth cell in a given column. I am using the formula below to add every 4th cell but it is just one that I found on line. Can someone explain to me the components of this formula so I can adapt it? =SUM((C2:C213) * (MOD( ROW( C2:C213)+1, 4)=0)) That is an array formula so it must be committed with Shift + Ctrl + <Enter>. here is how it works... Moving down through cells C2:C213 it looks at each 2 things. The value of the cell and the row that cell is on. If the row that cell is on is +1 is evenly divisible by 1 then ...

Why is the last used cell being used?
Hello! From what I can see, my last used cell is D48. From what Edit>Go To>Special>Last Cell is telling me, it's AF51. But when I go to AF51, it is blank, has no conditional formatting, has no data validation, and has no formulas. So why, is it the "last cell used"? I can find alot of "Find Last Used Cell" macros, but nothing that looks at your sheet and lets me know "This is the last used cell because it contains__________." ?? I appreciate any help with this. VR/ Lost Excel has a better memory than you--and it doesn't contract that used ra...

Replacing Contents of 1 Cell to Another.
My Spreadsheet looks like below. A B C 207 MDIM3030 3 TRUE 208 MDIM3030 FALSE 209 MDIM3034 1 TRUE 210 MDIM3034 FALSE 211 MDIM3038 6 FALSE 211 MDIM3039 4 FALSE I have sorted by Column "A" and done an EXACT on them. Now I need to know how to, for example on cell, B:207 (3) and move i to the blank cell below and so on through the Column. There ar hundreds of lines on the column, so I am looking for a...

Color cells that match on two sheet
Hi Everyone Using XL2003 I'm new to programming and this is my first try beside a couple of Userform. I've got a Vacation Planner on one sheet for 17 People Second sheet is a global view showing workdays for the year for all 17 people. Each cell is numbered to match with Julian date in Calctable sheet Third sheet is my Calculation table. Taking Start Date End date and listing them, Then converting those dates in to Julian dates without the year. I would like to colour the cells on the sheet "Globalview" that match the holiday This is a sample of my code "...

current time on excel
Right now I am using =Now() to display the current time. However, thi isn't perfect bc it only gives the time when there was last activity o the workbook. Is there a way to display current time even if excel i in idle -- Message posted from http://www.ExcelForum.com Hi pikapika13 It is possible to make this but I don't like it. you must use Ontime to update the worksheet http://www.cpearson.com/excel/ontime.htm -- Regards Ron de Bruin http://www.rondebruin.nl "pikapika13 >" <<pikapika13.18bp6f@excelforum-nospam.com> wrote in message news:pikapika13.18b...

Need help converting Range value in Excel
I have a macro that takes select information from an Excel worksheet and then creates a Chart. A problem I am running into is that one of the ActiveChart objects requires that the range be in a specific format. For example, early in my macro I have an array variable for the range "B8:J8". Is there a command I can use within my macro to change this to "R8C2:R8C10"? Thanks, Barry You can use the Range object's Address property to get an R1C1 style address for a range. Here's an example: MsgBox Range("B8:J8").Address(ReferenceStyle:=xlR1C1) -- Charles...

Copy Partial Cell Contents in Excel ? #2
I will definitely work through those responses and give them a try! Los this thread for a little while, but FOUND it again! Thank you very muc for your help Sirs! Best, Ma -- MacDubhga ----------------------------------------------------------------------- MacDubhgal's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1424 View this thread: http://www.excelforum.com/showthread.php?threadid=25899 ...

Paste copied data to specified sheet based on range
I have the following macro that copies data from the 'Daily Team Performance' sheet and then dependant on the content of cell B4 on that sheet, should copy this data to the respective named sheet on the 'buzz' workbook. The problem is that when I try to run it I get the 'Invalid Qualifier' message here: Set rDestination = Destsheet.Range("B4")... Can anyone see what I am doing wrong as the other option is screeds of code for every variation. Sub UpdateData() Dim Destsheet As String Destsheet = Range("Daily Team Performance!B4&...

Getting Active Control During Current Event
I have some code in my form's On Current event which changes focus to a particular control. I want the control to remain where it was when the user moved to the new record. But using Screen.ActiveControl or Me.ActiveControl in the OnCurrent event results in the error "the expression you entered requires the control to be in the active window" (2474). How can I determine the control that had the focus before moving to the new record, so that I can change it back there afterwards? Thanks! I think what you actually want is Screen.PreviousControl -- Doug Steele, Microsoft...

List dlls currently running in VB Application
I'd like to see what's being used by my VB application. Anyway to show the various dlls, ActiveX that are being used. One thought was to Enumerate Process, and the maybe look for the parent, but not sure if this approach will even work. Any suggestions appreciated. Thanks David "David" <NoWhere@earthlink.net> wrote in message news:%23mfJpH$NLHA.1868@TK2MSFTNGP05.phx.gbl... : I'd like to see what's being used by my VB application. : : Anyway to show the various dlls, ActiveX that are being used. : : One thought was to Enumerate Proce...

splitting contents of a cell #2
Hi all, I have a list of addresses split over five or so columns. One of the columns has the format "# street name". Is it possible to write a formula that lifts the street name out of that cell but not the number? so from a cell with "25 Hight Street" I would want just "High Street". hope that makes sense, thanks, luc if cell A1 has 25 HIGH STREET, the following formula in B1: =RIGHT(A1,LEN(A1)-SEARCH(" ",A1)) will extract HIGH STREET. -- icestationzbra ------------------------------------------------------------------------ icestationzb...

count number of cells
What is the formula to count the number of cells that start with a particular character? What I really want to do is count how the number of cells in a column that start with a through e. I have tried several things but can't quite come up with the magic formula. thanks for your help. =COUNTIF(A1:A20,"A*")+COUNTIF(A1:A20,"B*")+COUNTIF(A1:A20,"C*")+COUNTIF(A1:A20,"D*")+COUNTIF(A1:A20,"E*") -- David Biddulph "tagout" <tagout@discussions.microsoft.com> wrote in message news:2B624B5C-E73A-4481-A3E7-C9207F72DAB...

Summing only cells that have data and ignoring cells that have #DIV/0! in them
I have a spreadsheet that acts as a template. Because of that, there may be data in rows 2-10, but nothing below row 10. Data in columns A through Z feed formulas in columns AC through AL. Prior to putting any data in columns A through Z, the formulas in columns AC through AL are all showing #DIV/0! because of course there's no data in the cells those formulas are looking in. I want to sum the totals of some of the columns that in AC through AL, but of course if I only have data in rows 2-10, then the #DIV/0! message in the rows below 10 will not allow me to sum the entire column. I c...

Return a range based on a variable
Column A contains these five numbers: 9,2,3,5,8 Cell A7 contains the number 4 I need a formula that does the following: Sum the numbers in column A up to the number of rows in A7. Formula evaluates to 19 (9+2+3+5) Thanks, -- Art Hi, Try this =SUM(INDIRECT("A1:A" & A7)) Mike "Art" wrote: > Column A contains these five numbers: 9,2,3,5,8 > Cell A7 contains the number 4 > > I need a formula that does the following: > Sum the numbers in column A up to the number of rows in A7. > > Formula evaluates to 19 (9+2+3+5) ...

Checking for currency value between range
I have two txtboxes (txtWeeklyAllowanceLow, txtWeeklyAllowanceHigh) on a search form that I would like to use to find all weekly allowances on a table that fall within the range specified. Also if only a low amount is entered, then I would like all allowances greater than the amount to display, or if only a high amount then all allowances less than what is given. Any help with how I should code this would be great. thanks! Ok. Update. I seem to have the filter working for the high as well as the between, but now the low seems to not work. Here is the relevant code so far.. 'If W...

Move cell values
Hi, Need a help in Macros. Suppose i have the foll. data ColA Col B 1 w 2 re 3 dff --> Empty cells 1 ed 2 23 3 24 I need to copy the cell with 3 in colA two rows up, so it should be someting like this: ColA Col B 1 w 3 dff 2 re 3 dff --> Empty cells 1 ed 3 24 2 23 3 24 Thanks for help. Hi ........., I think you messed up your...

Displaying cell contents
I have a spreadsheet that has a formula in it in cells C1:J50. The cells are displaying 0 because there is nothing to calculate. How can I keep my formula for calculating when actual numbers are entered but the display will show an empty cell, not 0. Thanks, Blue Angel In your formulas you do some variation of this: =if(a1="","",yourformula) Regards, Fred "BlueAngel" <BlueAngel@discussions.microsoft.com> wrote in message news:87342869-89DD-4829-9EA0-0B077964B5DF@microsoft.com... >I have a spreadsheet that has a formula in it in ...

column of text cells
I have a column of country names entered as text, and I need to graph the number of occurances of each country. Help please! Got it. Thanks anyway! ...

how do I create floating cells so I can view them no matter wher.
useing a large spreadsheet i would like to have a totals box viewable from wherever i am and not have to keep scrolling to it or manually moving it every few days. any ideas. The simplest way to do something like this is to make the totals cell be A1 and freeze panes. "vk" wrote: > useing a large spreadsheet i would like to have a totals box viewable from > wherever i am and not have to keep scrolling to it or manually moving it > every few days. > any ideas. If you're using Excel 2002, or later version, you can use the Watch window to view specific cells: ...

Re: Separating addresses into multiple cells
I have been trying to separate addresses from 1 cell to multiple cells. I have 500 to do, is there a quick way to go about it. Example: 1234 S. West Street Indianapolis IN 46224 I need it to have the street name in 1 cell, City in another, State in another, and Zip in another. Thanks for your help. -- Rebecca ------------------------------------------------------------------------ Rebecca's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30751 View this thread: http://www.excelforum.com/showthread.php?threadid=504191 On Mon, 23 Jan 2006 15:22:23 -0600, Reb...

Dynamic range names
I use dynamic range names quite a bit, and although they appear correctly in the Names list accessed via Insert/Names/Define [eg. =Offset(Sheet1!$B$2,0,0, CountA(Sheet1!$B:$B),1)], I can't get them to appear in the NameBox dropdown list. This applies whether I create them as workbook (aka global) range names or worksheet (aka local) range names. What am I doing wrong? Thanks in advance. Nothing. That's the way it is. To test, use f5 (goto) and type in the name. -- Don Guillett SalesAid Software donaldb@281.com "LKG" <lkg2020@yahoo.ca> wrote in message news:6f5f51c...

getting cell color to allow alternating blocks of like value == like color cells
I have seen this asked a few places and some answers, but nothing that works. I want to simply alternate the color of a col so blocks of identical values have the same color. Thus A black A black B blue C black C black A blue B black C blue C blue C blue If you could get the cell color in a formula you could do it painfully as in a formula for black and a formula for blue applied to all but the first row. So the formulas would be along the lines of if x!=above cell and above cell is blue or x==above and above cell is black -> format black if x!=above cell and above cell is black or ...

Stop users leaving cells blank
Hi I have found some code from another thread which stops users leaving cells blank and displays a warning message when the user attempts to save the file: Dim cell As Range For Each cell In Sheets("Sheet2").Range("A1,A2,A3,A4") If IsEmpty(cell.Value) Then MsgBox "You must fill in cell " & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell Now, this works fine in the file itself and when I save it as a .xlsm file (I am using Excel 2007). However, the problem is that when you go to open the ...

How do I get a cell to show the fifth character of another cell?
I have a number in cell N7 which is 8 characters long. I am using =LEFT(N7,3) to refer to the first 3 digits of that cell and =RIGHT(N7,3) to refer to the last three digits. How do I get it to refer to the 2 center digits without showing any of the rest of the number? =MID(N7,4,2) cheers Pete -- (][ THIS EMAIL HAS BEEN SCANNED BY NORTON ANTIVIRUS ][) "dedsky" <dedsky@discussions.microsoft.com> wrote in message news:BA3B7CF5-9AF1-4BCB-8646-D5D59A0ADA84@microsoft.com... >I have a number in cell N7 which is 8 characters long. I am using >=LEFT(N7,3) > to refer to...

Put "Hidden" Message in Cell A1
I wanted to put a "hidden" message in cell A1 of Sheet 2 if the current date is greater than a given date. I tried: in cell E65536 (using white font): =IF(NOW()>4/14/04,(A1="Happy Birthday"),(A1="")) or =IF(NOW()>38091,(A1="Happy Birthday"),(A1="")) But neither worked [it would not put either condition in A1; but would work in the formula cell]. What am I doing wrong? Thanks in advance. RL ~ 4/16/04 One way: Worksheet functions can only return values to their calling cells, they can't affect other cells' values. A1: =...