# COUNTIF Formula - Value In The Cell Rather Than Reference To That Cell.

May 20, 2009

I've hit a problem when re-working some of my spreadsheets. Basically I have this bit of

## CountIf Contains Including Cell Reference?

Apr 29, 2014

I have a sheet that calculates what items are on any given transaction - this works well. I now want another table that counts how many times a certain brand comes up.

For example one item on a transaction might be "TENSOR REG TRUCK BLK 5.0 x2"

I need the table to look at this and count if tensor is in it (sheet name: 'Sheet 1') but I want it to look at the list of brands and and reference that brand rather than type it out individually.

## Countif Function With An Inequality Reference To A Cell

Apr 24, 2007

I have a problem using COUNTIF function. I want to count cells say in range A1:A10 that are smaller that a value in a cell let's say B1. Neither of these works:

=COUNTIF(A1:A10; "<B1")
=COUNTIF(A1:A10; <B1)

How to do it? I saw your answer of Dave to a similar problem, but I still do not understand it.......

## Add COUNTIF To Cell & Reference Closed Workbook

Jul 27, 2007

i'm trying to do a COUNTIF from an open workbook. The range I want for it is in another workbook, which I do not want to have open every time I run the macro.

This is my current
Sheets("Calls In-Out Trend").Range("ag18").Formula = _
"=COUNTIF('C:***[*.xls]sheet name'I:I, QXO)"
End Sub

editthe range is just so I can see if it's giving the right values, the range won't be like that once I get it working. I'll probably be using logic statements to place them in the appropriate cells. The * is just me taking out the directory names, its on the desktop)

It can copy cell values from another unopened workbook, but when I try to put in COUNTIF it doesn't want to compile/run the script.

That's basically the question. Here's some background and my aim:

I'm scanning row I:I for certain keywords which I then count up and put into corresponding columns of a chart.

## Change Formula Cell Reference Based On Another Cell Reference

Jan 8, 2012

How do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.

________A________________B_____________C_________D
2 Design begins__Proj OK______________10____10-Jan
3 Specs written__Design begins (A2)____5____15-Jan (D2+C3)
4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)

If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?

## Countif Based On Alpha And Color Of Cell In Same Row But Absolute Column Reference

Apr 28, 2014

I am currently using a color count function to both count by color and count by color and cells containing certain characters, such as # or %. This works great.

However, I need to modify the UDF to count all the cells in a range in columns C:Z that contain alpha, AND IF the Interior.ColorIndex of a cell in column B within the same row of the counting formula equals the Interior.ColorIndex of a cell in column B within the same row of the selected cell in the range.

Basically, Column B is a header row, and I want to count the cells in a range in each column C:Z if they contain a name AND their corresponding header cell's color in column B matches the color of the header cell in the row containing the formula.

## Excel 2007 :: Formula To Return First Cell Reference In Another Cell Formula

Oct 1, 2011

Version: Excel 2007 WinXP

I'm basically looking for something almost like an inverse function to INDIRECT. This function would first look at a cell's formula as a text string, parse out the first valid cell reference in A1 format, and return that cell as a text string.

Detail: I have a spreadsheet with cells that point to other values. I would like to get only the row number from the first cell reference in the formula residing in a given cell. For example:

Suppose A1 has the formula =AL267. and A2 has the formula =SUM(AL94:AL235)

I would like a formula in B1 that returns the text string, "AL267" so that I would know this is the first reference.

Ideally it could be dragged down to B2 such that it returns the text string "AL94" (and not "AL235") because AL94 is the first cell reference in A2's

Currently I am copying the formulas after hitting ctl+` and pasting that text into a text editor, followed by text operations to manipulate the results into the desired values. Any solution that didn't involve going out to notepad.

## Circular Reference: Cell References In The Formula Refer To The Formula's Result, Creating A Circular Reference

Aug 14, 2006

I have the following formula in cell L51 of all sheets calculating the volume depending on the monthly index that is chosen from the drop down menu in a particular sheet. =If(MIndex=0, SUM(D33:L50),If(MIndex=1,SUM(D34:L50),If(MIndex=2,SUM(D35:L50), 0))). I am getting the following message and I do not understand what it is about.

Microsoft Office Excel cannot calculate a formula. Cell references in the formula refer to the formula's result, creating a circular reference. Try one of the following

## Use Text In A Cell For A Formula As Cell Reference To Create Range

Mar 10, 2013

I have a list of headings and items and I have a set of formulas that work out depending on the heading what items are listed.

Say theres 10 items and the heading starts at C4 and that heading has 10 items, so it puts "C5" as text in G1 and "C15" as text in G2 so i now know my cell range of items

How can i use the text in those cells to put in a formula to call that as a range?

If I use the indirect formula it shows me the value of the cell, but im after using it to reference the cell

## Maintaining Cell Reference According To Its Physical Position From Formula Cell?

Jan 29, 2014

I need to make a cell display the contents of the cell immediately below it, regardless of if there are rows inserted at the referenced row or above or below it on the worksheet. ALSO I need to do this at several additional cell locations down the worksheet. Such as : A1 displays A2, A4 displays A5, A7 displays A8 etc.

Example: I want A1 to display whatever is in the cell immediately below it. In this case A2. I need to be able to insert several rows at row 2 and have whatever new value is now in the A2 position displayed in A1.
After the rows are inserted I need the cell that was in position A4 to continue to display the contents of the cell below it.

I tried using the formula in A1 of: =INDIRECT("A2") and it works good except when I insert the rows my similar formulas below the area where the rows were inserted continue to refer to the specific original cell and not the one positioned now below it.

## Cell Reference :: Formula To Reference New Cells

Feb 15, 2010

I have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.

EXAMPLE:

12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.

## Copying A Cell To Cell Reference Created By A Formula

Jul 25, 2013

I have run an INDEX and MATCH formula to give me a resultant cell reference number. I now want to copy a cell to this specific cell reference using another formula.

For example the data I want to copy is in cell A1 and in cell A2 I have the result of the index formula (B1).

I know how to manually do a formula for this but wanting to know if there is a uniform formula that I can use that looks at the formula result of cell A2.

## Convert Cell Reference Text To Cell Formula

Aug 18, 2009

I am wanting to convert a cell reference text
"=\$A\$1"
to an actual cell reference
=\$A\$1

Manually I can go through each cell and click F2 + Enter and Excel automatically changes it.

I have tried recording a macro whereby I click through each cell with F2 + Enter but the VBA writes the actual formula "=\$A\$1" rather than the process. This does not work as the cell reference is variable.

I'm NOT wanting an external cell to convert it for me
i.e. INDIRECT(CELL)
because I am wanting to copy the answer to another independent spreadsheet

I'm NOT wanting to paste values
i.e. return the answer from cell \$A\$1
because I want the cell reference to remain within the cell.

## Formula/Function Reference To Cell Address In Another Cell

May 22, 2008

how can I use the cell function inside a NPV fuction as one argument of the start for the NPV string to calculate?

## Formula Reference Cell Address In Cell

Jan 15, 2008

I have 2 sheets. In the first sheet, I have 3 columns. The first column contains transaction codes. The 2nd column contains the destination code (like a mapping cell where the value of the 3rd column would go but in a different sheet/2nd sheet) and the 3rd column contains the values for it. Would it be possible to reference a destination in a cell itself? Or should I just code it individually? I wouldnt want to reference each data one by one because the total would be 480 reference cells. Is there an easier way?

## COUNTIF Formula Based On Cell Formatting?

Mar 18, 2010

Is there a Countif that works on the criteria of the font being 'Red' or 'Bold' ? This needs to work based on conditional formatting I have on the cells in question.

## Excel 2003 :: Referring To Cell Value In COUNTIF Formula

May 7, 2013

I am using Excel 2003

Cell B13 contains the row number of the first row of data on the sheet and B14 contains the row number of the end row of data. This is because the number of rows vary over time.

I'm trying to do a COUNTIF to test for duplicates in column R (contains integers) and I want to specify the range to count by referring to the value in B13 and B14 in the formula and so I have the following which returns FALSE for some reason even if the value in R19 appears more than once in the range ...

=COUNTIF(INDEX(R:R,\$B\$13):INDEX(R:R,\$B\$14),R19)>1

## Using DOB Formula As Reference Cell

Jul 28, 2012

I am using the DOB formula of =DATEDIF(AA19,NOW(),"y") & " years, " & DATEDIF(AA19,NOW(),"ym") & " months, " & DATEDIF(AA19,NOW(),"md") & " days". In the next cell I want it to look at the previous cell and determine if "older than 2 years". I have tried the "search" and isnumber, but I keep getting the wrong answer.

## Formula To Reference A Cell

Jul 26, 2009

Hello,

I am trying to create a formula in Excel to preform a "SUM" on cells that only have a letter Y (as in "yes") in the cell next to them.

For example

## Reference Cell With A Formula?

May 11, 2012

I need to display cell AV13 from sheet 2 on sheet 1. To do this I go to sheet one and in the cell I type =Sheet2!AV13 and voila I get my value. My problem is AV13 is determined by two list boxes, one finds the column AV (named the cell that holds this value as StageColumn) and the other finds the row (named SubCatRow). What I want to do is have a cell that gets this information from Sheet 2. I tried to concatenate the string, but all I get is the text.

The concatenation string was =concatenate("='Sheet2'!",F25)
I have F25 containing the formula =Address(StageColumn,SubCatRow)

## Getting Cell Reference Of Formula?

Mar 24, 2013

i have a table of data and a formula at the right which gives the max of that data and would like to get the row header and column header of the cell that is giving me the maximum value

e.g on the below the maximum value is 24346.3 and i would like to have in another cell Australia Diversified REITs

Diversified REITs
Homebuilding
Industrial REITs

[Code].....

## Reference The Formula Of Another Cell

May 22, 2009

I am trying to figure out how to reference the formula from another cell. For example, say cell A1 has the formula =indirect(address(row(),2)). Assume B1 equals 10 and B2 equals 20. The formula in A1 would yield 10. How can I make A2 reference the formula in A1 so that A2 yields 20? I need it to actually reference the formula in A1, simply copying and pasting A1 or dragging A1 will not suffice.

## Use A Cell As A Reference In A Formula

Feb 18, 2010

I currently have the formula =+'Mar-Final'!\$C10*M3/N3.
I would like to have the 'Mar part be input from a cell.

For instance, if I put Feb in cell A3, then the formula would look like ='Feb-Final'!\$C10*M3/N3.

## Fix Formula Cell Reference

Jul 31, 2008

I have a column (A1:A26) with a series of numbers which I want to multiply by a second variable in another column (Sheetb:C1). So I write a formula for a new column - =A1*Sheetb!C1. I want to multiply each value in (A1:A26) with just the value from Sheetb:C1. However, when I drag the 'fill series' pointer down, it automatically increments the cell its is multiplying by i.e. 1st cell is A1 X SheetB!C1, 2nd cell is A2XSheetB!C2... However I want it all multiplied by SheetB!C1.

## Use A Worksheet Reference Cell In A Formula?

Apr 10, 2014

I have a formula to extract the worksheet name and add the "!" to it so it can be used in a copied formula... I'm just having trouble writing the formula to reference the text in the cell with the worksheet name. If it's easier, I also have the list of worksheets without the "!" - e.g., Sheet1, Sheet2, Sheet3, etc...

I want the formula to calculate, in my example, the sum of 2 cells on a different worksheet.

## Formula With Cell To Reference Other Workbook?

Mar 19, 2014

So finally got my sumrpod working using this formula

=SUMPRODUCT(1*('Q:Documents13-14 FCGP CONTRACTING13-14 TrackingSouthPac - Team Use Only[South Pac Load Tracker - MAR14.xlsm]SPECIALS TEAM NZ'!\$C8:\$C10000=A5))

Is there a way i can link 'Q:Documents13-14 FCGP CONTRACTING13-14 TrackingSouthPac - Team Use Only[South Pac Load Tracker - MAR14.xlsm] to a cell so that when the next months tracker is created and the filename changes i simply change the filename in the cell which im using to reference the filepath/filename.

e.g A1 = South Pac Load Tracker - MAR14.xlsm

Formula would read =SUMPRODUCT(1*('Q:Documents13-14 FCGP CONTRACTING13-14 TrackingSouthPac - Team Use Only[' & a1 & ;]SPECIALS TEAM NZ'!\$C8:\$C10000=A5))

## Unable To Use Cell For Reference Which Has Formula?

May 4, 2014

I have one coloumnA with formula =IF(F4="","",(H4-F4)) ------ the result I want to use for reference in which I am unable to use the result value using below formula.
I have one more cell B with month & date

Currently using formula =SUMPRODUCT((TEXT(A4:A1501,"mmmyyyy")=\$B4&\$C4)*('Credit Cards&Loans Transactions'!J4:J1501))
Cell B4 has month & C4 has year.

J4:j1501 cell has the formula "=IF(F4="","",(H4-F4)) " , I want to use the result of cell in above formula.

## Varying A Cell Reference In A Formula

Jan 6, 2009

I am trying to identify some combination of formulas or functions that will allow me to adjust the cell reference within a function based on a value in another cell. For example, I have a series of values listed from cell C50 to cell W50. I want to use a function to calculate the internal rate of return over a range of the cells: =IRR(C50:W50)
So far, so good.

However, I want the size of the range to be determined by a value entered in another cell, let's say B2. If the value in cell B2 is "5", I would want the formula to consider only the first five columns: =IRR(C50:G50). If the value in cell B2 is "10", I would want the formula to consider only the first ten columns: =IRR(C50:L50).

## Cell Reference For A Formula To + 5 Each Time

Apr 20, 2009

I need the cell reference for a formula to + 5 each time I copy down, however, it seems to only + 1 no matter what I try?! The formula is a simple =sum(A4:A8). I would like to be able to drag it down so that the next one states =sum(A9:A13) and so on.

## Replace Name In Formula With Cell Reference

Sep 15, 2009

In the following formula, I would like to replace STARK with the value in cell D33. I've tried a couple of different things using &s but no luck yet. I think I'm just missing something really simple.