Using a value from a cell inside a formula!

Hi!

I've got two cells that contain the start and end row of a matrix in 
sheet.

I want to use these row numbers in a =COUNT.IF formula to count th
number of specified instances in this matrix.

But my problem is this; how can I use the values in the two cell
inside the COUNT.IF formula??

I should look something like this;

=COUNT.IF(F"cell1":F"cell2";"=argument")

As you can see, the column (F) is specified in the formula but the ro
number needs to be fetched from cell1 and cell2.

Can anyone help me with the correct syntax?

Thanks!

-M

--
marsupilam
-----------------------------------------------------------------------
marsupilami's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=269
View this thread: http://www.excelforum.com/showthread.php?threadid=39811

0
8/23/2005 11:43:22 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
603 Views

Similar Articles

[PageSpeed] 22

try
=countif(offset(indirect("F:"&cell1),0,0,cell2-cell1+1),"argument")

"marsupilami" wrote:

> 
> Hi!
> 
> I've got two cells that contain the start and end row of a matrix in a
> sheet.
> 
> I want to use these row numbers in a =COUNT.IF formula to count the
> number of specified instances in this matrix.
> 
> But my problem is this; how can I use the values in the two cells
> inside the COUNT.IF formula??
> 
> I should look something like this;
> 
> =COUNT.IF(F"cell1":F"cell2";"=argument")
> 
> As you can see, the column (F) is specified in the formula but the row
> number needs to be fetched from cell1 and cell2.
> 
> Can anyone help me with the correct syntax?
> 
> Thanks!
> 
> -M-
> 
> 
> -- 
> marsupilami
> ------------------------------------------------------------------------
> marsupilami's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2698
> View this thread: http://www.excelforum.com/showthread.php?threadid=398110
> 
> 
0
BJ (832)
8/23/2005 12:16:06 PM
=COUNTIF(INDIRECT("F"&cell1&":F"&cell2),"argument")

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"marsupilami" <marsupilami.1u7umc_1124798705.3047@excelforum-nospam.com>
wrote in message
news:marsupilami.1u7umc_1124798705.3047@excelforum-nospam.com...
>
> Hi!
>
> I've got two cells that contain the start and end row of a matrix in a
> sheet.
>
> I want to use these row numbers in a =COUNT.IF formula to count the
> number of specified instances in this matrix.
>
> But my problem is this; how can I use the values in the two cells
> inside the COUNT.IF formula??
>
> I should look something like this;
>
> =COUNT.IF(F"cell1":F"cell2";"=argument")
>
> As you can see, the column (F) is specified in the formula but the row
> number needs to be fetched from cell1 and cell2.
>
> Can anyone help me with the correct syntax?
>
> Thanks!
>
> -M-
>
>
> -- 
> marsupilami
> ------------------------------------------------------------------------
> marsupilami's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=2698
> View this thread: http://www.excelforum.com/showthread.php?threadid=398110
>


0
bob.phillips1 (6510)
8/23/2005 12:50:18 PM
marsupilami Wrote:
> Hi!
> 
> I've got two cells that contain the start and end row of a matrix in 
> sheet.
> 
> I want to use these row numbers in a =COUNT.IF formula to count th
> number of specified instances in this matrix.
> 
> But my problem is this; how can I use the values in the two cell
> inside the COUNT.IF formula??
> 
> I should look something like this;
> 
> =COUNT.IF(F"cell1":F"cell2";"=argument")
> 
> As you can see, the column (F) is specified in the formula but the ro
> number needs to be fetched from cell1 and cell2.
> 
> Can anyone help me with the correct syntax?
> 
> Thanks!
> 
> -M-

Use an INDIRECT function and concatenate with the &

eg you can refer to a cell as follows

=indirect("A" & B23 & ":A" & B24)

so your formula would look something like

=countif(Indirect("F" & cell1 & ":F" & cell2, argument)

--
ronthedo
-----------------------------------------------------------------------
ronthedog's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2650
View this thread: http://www.excelforum.com/showthread.php?threadid=39811

0
8/23/2005 12:57:15 PM
Another way, which I believe, is non-volatile:

=COUNTIF(INDEX(F:F,Cell1):INDEX(F:F,Cell2),argument)

-- 
HTH,

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


"marsupilami" <marsupilami.1u7umc_1124798705.3047@excelforum-nospam.com>
wrote in message
news:marsupilami.1u7umc_1124798705.3047@excelforum-nospam.com...
>
> Hi!
>
> I've got two cells that contain the start and end row of a matrix in a
> sheet.
>
> I want to use these row numbers in a =COUNT.IF formula to count the
> number of specified instances in this matrix.
>
> But my problem is this; how can I use the values in the two cells
> inside the COUNT.IF formula??
>
> I should look something like this;
>
> =COUNT.IF(F"cell1":F"cell2";"=argument")
>
> As you can see, the column (F) is specified in the formula but the row
> number needs to be fetched from cell1 and cell2.
>
> Can anyone help me with the correct syntax?
>
> Thanks!
>
> -M-
>
>
> -- 
> marsupilami
> ------------------------------------------------------------------------
> marsupilami's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=2698
> View this thread: http://www.excelforum.com/showthread.php?threadid=398110
>

0
ragdyer1 (4060)
8/23/2005 9:37:22 PM
Thanks alot guys!

-M- :

--
marsupilam
-----------------------------------------------------------------------
marsupilami's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=269
View this thread: http://www.excelforum.com/showthread.php?threadid=39811

0
8/24/2005 6:06:49 AM
Reply:

Similar Artilces:

cell
Are there any way to add text and a function in the same cell? For example Hello =sum(XX:XX) Try something like ="Hello "&SUM(A1:A10) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "danne" <modig123@hotmail.com> wrote in message news:0a7f01c36335$498056f0$a401280a@phx.gbl... > Are there any way to add text and a function in the same > cell? For example Hello =sum(XX:XX) Sure, for example: ="Hello = "&SUM(A1:B1) For more info, try MVP Debra Dalgleish's &...

Macro or Formula needed to search data in cells
Hi I am looking for a way to create a formula/macro to do the following: My worksheet setup: A1:A30000 C1:C5000 01 02 03 04 05 06 07 08 09 10 01 04 05 06 08 09 12 22 23 27 02 03 04 05 06 07 08 09 10 11 01 03 05 06 07 08 09 14 22 32 03 04 05 06 07 08 09 10 11 12 04 05 06 07 08 09 10 11 12 13 etc Each cell contains a 10 number sequence. The range A1:A30000 is my randomly generated sequence. And C1:5000 is my database of archived sequences. If I wish to check if the combination in cell A1 is anywhere in the range C1:5000. I use the formula. =IF(COU...

Dynamic Bar of Pie Chart: Series1 Label & Value Issue
I have created a Bar of Pie chart and everything works great. I even have named ranges on my data so that the bar portion automatically updates when the data changes. However the Series1 Label on the pie itself does not change to reflect the new total so that it is the sum of all the values in the right column below. Does that make sense? I have one series and my data looks like this: Contracts 100 Name1 100 Name2 150 Name3 200 Name4 75 Name5 50 Name6 25 The pie portion has Contracts & Other as labels with the totals of...

Forwarding Using Mail Enabled Users
Are there any differences forwarding mail to an external recipient using a mail enabled user vs a mailbox enabled user forwarding to a contact aside from the fact that the mail enabled user doesn't have a mailbox? In news:eRPSivG0FHA.3408@TK2MSFTNGP09.phx.gbl, Steve Fukumoto <steve.fukumoto@necsam.com> typed: > Are there any differences forwarding mail to an external recipient > using a mail enabled user vs a mailbox enabled user forwarding to a > contact aside from the fact that the mail enabled user doesn't have a > mailbox? A mail-enabled user has an external...

negative values on axes
hi there; this is my first mail here. well my x axes start from -2 and my Y axes data start from -5, I change both to -5 (scale) they are shown (XY scatter dots) as cross, like + sign. what I need: how to change the graph punt the joint axes point from 0 to -5? so the chart shws as usual not like a cross. Best Darius Hi, Change the Values axis crosses at to -5 for both axis. This is an option on the Scale tab of the Format Axis dialog. Cheers Andy Darius wrote: > hi there; > this is my first mail here. well my x axes start from -2 and my Y axes data > start from -5, I chang...

How to export values from XY (Scatter) graf?
Is it possible to export each x and y value from XY (Scatter) graph? ...

Hide rows with value "Hours"
I would like to toggle rows to hide/unhide based on the value "Hours" in column B. I think filtering is an issue to some blank seperator rows and merged cell headings. There are no merged cells involving Column B. Thanks. Public Sub ProcessData() Const TEST_COLUMN As String = "B" '<=== change to suit Dim i As Long Dim iLastRow As Long With ActiveSheet iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).row For i = 1 To iLastRow .Rows(i).Hidden = .Cells(i, TEST_COLUMN).Value = "Hours" Next i End With ...

Compare cells, update based on Ifs
I need to compare cells to the cell above them and to the right of them. Based on the comparisons I'll need to update the original cell with one of those adjoining cell values. After I finish with one column then I need to repeat the procedure on the column to the LEFT of the original column. I know IF, THEN and ELSE statesments but I don't know VB for Microsoft Office products. Range could be all 65,000+ rows on a workseet Start on ColumnJ, Row2 If ColumnJ, Row2 is Null _ If ColumnK, Row 2 is Not Null _ If ColumnJ, Row 1 is Not Null _ ColumnJ, Row2 Value is ...

If cell value is greater than another cell value, clear contents.
Okay, hopefully this is the last question today! I've found the below formula in one of the posts here, but my brain has completely failed on me and I'm not sure how to adapt it to my requirements. I have a value which is entered by the user after a prompt, that populates in cell G4 in a sheet called 'Lookup'. For arguments sake, the current value is 30/11/09. I have another worksheet, 'Data', which has a column populated with dates (say column B). I'd like a macro that looks in column B on the data sheet, and clears out any dates which are gr...

Why does linked data from blank cells become a zero?
When linking data within the same workbook (to different tabs) any cell that is empty - shows up in the new tab as a ZERO (0).....what am I doing wrong? I want those cells to remain blank. That's just the way it is .. Instead of : =Sheet2!A1 you could use an error trap: =IF(Sheet2!A1="","",Sheet2!A1) Alternatively, we could suppress the display of extraneous zeros in the entire sheet via: Click Tools > Options > View tab > Uncheck "Zero values" > OK -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot&g...

Date chart formula question?!?1
Hi all, In a very old spread sheet i was using this formula =IF(OR(AND($D7<G$6,$E7>F$6),AND(F$6>=$D7,F$6<=$E7)),".","") and it worked for dates pre 2009. However, i now wish to use it for dates between 2010 - 2011. Each column represent a week and the rows will indicate and project or task. can anyone help? There is nothing it that formula that will make it work for pre-2009 and not for later years. Perhaps you need to look at your data values? If you have a problem, you could tell us what values are being fed into the formula when it works...

Use the same name on multiple sheets in same workbook
I am new to Excel 2002. In prior versions I could create sheet specific names easily. With Excel 2002 every time I try to re use a name on another sheet (same workbook) the name gets moved to the new sheet. I use several workbooks (one book for sales, one for cost of goods, etc), with 12 sheets named for each month of the year. Each sheet is a duplicate of the first. As the year progresses I fill in the data at the end of each month. In the past I used the same names(Gross.Sales for example) on each sheet in the workbook. I then can build monthly summary reports using these names...

IF Formula #6
Does anyone know a way of returning a picture instead of a value in an IF formula? eg =IF(A1=JIM, <picture>, 0) Any help very much appreciated!! Cheers in advance! Hi do you need the pircote or would a hyperlink also suffice?. In this case use something like =IF(A1=JIM,HYPERLINK("C:\temp\test.jp"), "") -- Regards Frank Kabel Frankfurt, Germany Jamie Bishop wrote: > Does anyone know a way of returning a picture instead of a value in > an IF formula? > > eg =IF(A1=JIM, <picture>, 0) > > Any help very much appreciated!! > Cheers in advan...

How do you ensure that null-valued elements are not serialized?
I am using complex types in order to support serialization/deserialization of floating point numbers, since floating points can't be null. I've seen how to suppress attributes that are "not specified", such as having a float member called Value, and a bool member called ValueSpecified. This instructs the XML Serializer to omit that attribute altogether if it wasn't "Specified". But how can I tell it to omit the XML element altogether? Here's the problem: I deserialize an object that looks like this: <person> <name>Joe<...

Error when send email from campaign response using template through workflow
Hello, There is an invalid argument error shown in workflow records if send email from campaign response using template. Is it impossible to send email in this way ? ...

Multiple Cell Formats
Is it possible for a column to have more than one format, but for those formats to be similar. Eg. I would like to have a multi-format date column. The format can either be Month-Year (Jan-99) or Year only (1999). (I would even like to have something like Jan/Feb-99, but I can live without that.) Any help would be appreciated. Thanks. Hi With true Excel dates in column A e.g. 01 Feb 07, in column B =A1 Format>Cells>Number>Custom> mmm-yy will give Feb-07 or =TEXT(A1,"mmm-yy") =A1 Format>Cells>Number>Custom> yyyy will give 2007 or =TEXT(A1,"yy...

Capture initial value of text box
Not sure why I can't seem to get this to work, but here's what I'm trying to do: I have a text box, 'Actual_Due_Date', tied to a field in a table. In the same table is a text box named 'Org_Due_Date'. When the initial value is set in the 'Actual' field, I want to capture and store it permanently in the 'Org' field. I have two forms, a New Project form and an Edit Project form. My thought was to just setup a simple macro in the New Project form in the 'Actual' field that says After Update set the 'Org' field equal to the 'A...

Invalid Argument Error when using 1/1/08 as a date
Hi all, Not sure if this is a known issue - can anyone else create a record that contains a date field with the value of 1/1/08 or 1/1/07, save it and then open it again without getting an "Invalid Argument" error. I am able to produce this error across different environments, different entities and different date fields. Would be interesting to hear more about this. Thanks Hi there, Just to let you know, I just created a task in CRM 4 and set the due date to 1/1/08 and it worked without any issues. This is a field displayed as a Date/Time box, on not just Date. I don...

Use of InstallShield
Hi, I have got InstallShield with VC++, and I would like to use it to create a setup for various types of project, e.g. a game, activeX control, etc. However the project wizard only allows you to choose an .exe, and it makes you choose between 'database application', 'finance application', etc. What if my product isn't an .exe, say it's an .ocx, or what if it isn't one of these predefined types of program? I've no idea on where to start with the blank project option. I added my .ocx to the 'components' view and then tried pressing Ctrl+F5, but a...

Conditional formating using VBA
Hello I have to format cells (I4:J37) if cells (I43:J76) are: 100%-91% (green) 90%-76% (blue) 75%-50% (yellow) <50% (red) How do I write that in VBA? (I am new to VBA so any help would be appreciated) Many thanks Tracey Hi Tracey, I asume your users will enter a value in the range("I43:J76"). In that case you case use the Worksheet_change(Byval Target as Range function like so: Private Sub Worksheet_Change(ByVal Target As Range) ' Check if only 1 cells value is changed If Target.Cells.Count > 1 Then Exit Sub ' Check if changed c...

help with formula #24
$40.50 when S is $795.00 I need help figuring this formula out where am I going wrong? I keep getting the wrong figures. This is the formula: =IF(S118<=25, 0.0525*s118) + IF(AND(s118 >25, S118<=1000), 1.3125 + 0.0275*(S118 -25)) + IF(S118>1000, 28.125 + 0.015*(S118-1000)) Up to $25 will be charged 5.25% , 5.25% of the initial $25.00 ($1.31), plus 2.75% of the remaining closing value balance for $25.01 up to $1000, over $1000.01 5.25% of the initial $25.00 ($1.31), plus 2.75% of the initial $25.00 - $1,000.00 ($26.81), plus 1.50% of the remaining closing value balance (...

what function would I use here?
In rows 1 to 192 I have a letter in just one of the columns D,E,F,G,H,or I. I want to fill in column C so that it will have the letter that is in column D thru I. What is the function that I use? I tried =MAX(d2:i2) but that doesn't work. I suppose the MAX function is for numbers only. Is it a FIND function or something different? Thanks in advance. -- Larry Smith "In this country anyone can grow up to be President. That's the risk you take." Adlai Stevenson You could put this formula in C2 (for example): =3DD2&E2&F2&G2&H2&I2 and then c...

Is there an add-in that will lock the cells like later versions of Excel?
I'm using 97 and for 99.9% of everything I do I works fine except I can't lock cell format so there can only be data entry. I would be nice if I could do that. Marc Hi Marc, Can you be more specific about what you want and don't want. "Marc" <mcnr(N_O-S_P_A_M)@mindspring.com> wrote in message news:QThPf.1161$sL2.501@newsread2.news.atl.earthlink.net... > I'm using 97 and for 99.9% of everything I do I works fine except I can't > lock cell format so there can only be data entry. I would be nice if I > could do that. > > Marc > > ...

Word 2003: Possible to Use both Forms and Tracked Changes Protect
I've not been able to figure out a way to protect a document so that one section is protected for filling in forms only, and another section is protected in tracked changes mode. I want to create better contract templates for use by our sales teams (thus the forms protection - they can only fill in the gray fields and not alter other sections of the template), while allowing our clients to made modifications to the terms in the subsequent pages. I find Word protection features very limited. Hopefully it's better in 2007. Thank you. A document can only have one type ...

Number of cells that have same values
Imagine there are two columns of cells, each row of two cells are corresponding to each other. How to find out the number of rows where the two corresponding cells have the same value? Assume your Columns are A and B, and your rows go down to 20, Array enter this formula (CTRL+SHIFT+ENTER): =SUM(IF(A1:A20=B1:B20,1,0)) Change the column and row references to fit your data. -- Regards, Dave "Lingyan Hu" wrote: > Imagine there are two columns of cells, each row of two cells are > corresponding to each other. How to find out the number of rows where the two > corres...