# Using Countif Formula With Dynamic Range

Nov 6, 2013
I am using the CountIf function (in column B) to count the number of events called "EVENT1" from columns C (Time) and D (Events), but the range needs to include all cells which, at the current B row, have the same value as in row A. I want to use a dynamic range so that I can account for the repeats.

Column A

Column B

Column C

Column D

Time

Count

Time

Events

1

=Countif(,"EVENT1")

1

event0

[Code] .....

View 6 Replies
ADVERTISEMENT
Feb 15, 2013

I have column A fill with dates and column B with customer code. I'm looking to count the number of time the customer code (column B) is repeating but ONLY in previous year. Meaning that the count range must be adapt for each customer code. Will a countifs is able to work with this request?

View 6 Replies
View Related
Dec 3, 2008

I have a data set that alters each week which means at the moment I have to update the ranges manually each time. I would like to use a dynamic range and think I need to use the Offset and CountIf functions. Both the start and end of the ranges are dynamic. The range is based on grouping the numbers in Column E (LocNo)

What I would like to do or think the way to achieve this is by:

First looking at Column E

Count how many cells are equal to criteria. (CountIf)

offset 5 columns left from the first cell that meets the criteria (Offset -5)

offset X rows down based on how many cells meet the criteria (Offset X)

I've include a spreadsheet spread sheet which 'should' help illustrate the problem showing the ranges I would like and how the data changes each week.

View 5 Replies
View Related
Jan 5, 2009

I have certain cells in column A2:A22 that have names of people. I want a formula in Cell A23 that gives me a total of how many names are in that range. I know this is simple, but how do I put my criteria that if a cell is not blank to count it?

View 4 Replies
View Related
Jun 4, 2014

I have a drop down list in a column called Report Type (example below).

Report Type - Drop-Down Menu in Column F

Business/Operational/Work Plan

Budget Report

Performance Report

Program Quarterly Report

Program Mid-Year Report

Program Annual/Year-End/Final Report

Service Quarterly Report

Service Mid-Year Report

Service Annual/Year-End/Final Report

Financial Quarterly Report

Financial Mid-Year Report

Financial Annual/Year-End Report

Auditied Financial Statements

In-Year Reallocation

Annual Reconciliation Report

SRI Report

Other Report

I need to count all the cells that have: Budget Report, Financial Quarterly Report, Financial Mid-Year Report, Financial Annual/Year-End Report, Audited Financial Statements, In-year Reallocation, and Annual Reconciliation Report

Is this possible with a countif formula?

View 4 Replies
View Related
Jan 13, 2010

I have a dynamic range formula that refers to a list that I've set up in row 65 of my sheet. Each time I go to the cell the has the data validation referred to in this range, the drop down list shows the list from the last entry and with more spaces than needed. =OFFSET('GROCERIES AMEX'!$E$65,0,0,COUNTA('GROCERIES AMEX'!$E:$E)-15,1)

The " -15" is the number that varies all the time. Is there any way to remedy this so the formula behaves as if the list was located in Row 1?

View 3 Replies
View Related
Apr 23, 2013

=sum(b2:index(b2:d5,0,match(a7,b1:d1,0)))

I want the dynamic sum to change to given two criterias that is

1 row in index formula needs to lookup codes in a2:a5(match function do no good cause i got multiple values)

2 column in index formula refers days in b1:d1

a

b

c

d

1

codes

day1

day2

day3

[Code] .....

View 9 Replies
View Related
May 14, 2009

I am working with a dynamic range on sheet name "DataPrep", the named range is "TGSDataSourceRange".

I need a formula that will look from row 2 down to the last row for columns "A-H".

The purpose is to identify the active range. I think the function is "Counta", but I can't get it right.

View 9 Replies
View Related
May 22, 2012

Building a formula to calculate the total downtime for a specific configuration item (in this example: Wintrack).

See table below:

Task.NumberConfiguration ItemDowntimePriorityINC0028900Wintrack602INC0028900Data Tree201INC0028901Wintrack151INC0028903FAST1901INC0028904Wintrack102INC0028905Wintrack53

The table above will vary in row numbers every month so I would like to use dynamic range. I will be calculating total downtimes for each configuration item every month and would like those totals reflected on a separate Excel sheet. .

View 4 Replies
View Related
May 29, 2013

I need to use the valve in the last row in column 27 and subtract the value in column "Y". Looking to modify this "R37C27" to dynamic range

Range("AG1:AG" & lr).FormulaR1C1 = "=IF(R[2]C[-13]=""Nanner Table"",R37C27-RC[-8],RC[-6])"

View 1 Replies
View Related
Sep 24, 2009

i'm using a formula to derive some data in a column of an excel tab. it is -

=IF(H2=5,(CONCATENATE(YEAR(E2),(0),MONTH(E2))),CONCATENATE(YEAR(E2),MONTH(E2)))

how can i add a dynamic range to this so that it updates automatically when the number of rows expand?

View 9 Replies
View Related
Feb 15, 2010

I use the function OFFSET for names in order to be able to add new rows below the table without the need of changing the range of the name.

However, there are error produced if I work it this method.

I am not sure whether it is due to the formula but maybe because of the data or format validation ? Generally, in my cells I include

if(CellRef="";"";formula)

in order to copy the formula down. Pls also look at the defined names ....

View 9 Replies
View Related
Oct 12, 2007

I am trying to insert a formula into the cells in a column dynamic range.

I never know how long the column will be from instance to instance.

I have been trying to use the following code,

Sub insertf()

'

' insertf Macro

' Macro recorded 10/11/2007 by bbactadmin

'

'

Do Until myrange.Value = ""

ActiveCell.Range("f10").Select

ActiveCell.Offset(1, 0).Range("f1").Select

Range.Formula = _

"=VLOOKUP(LEFT(E11,3),fam,2,FALSE)"

ActiveCell.Offset(1, 0).Range("f1").Select

Loop

End Sub

I am trying to insert the lookup in all of the cells in the column within the dynamic range.

View 8 Replies
View Related
Jul 1, 2007

Does COUNTIFS not work with named ranges/dynamic ranges? Everytime I try, I get a #VALUE! error. These are my 2 dynamic ranges:

ResultsM=OFFSET([results_list.xls]Results!$M$4,0,0,COUNTA([results_list.xls]Results!$M:$M),1)

ResultsQ=OFFSET([results_list.xls]Results!$Q$4,0,0,COUNTA([results_list.xls]Results!$Q:$Q),1)

The formula is simply:

=COUNTIFS(ResultsQ,"GENDER",ResultsM,"MALE"

However, this does NOT work. it returns the #VALUE! error. However, odly enough, when I use column references, it works:

=COUNTIFS([results_list.xls]Results!Q:Q, "GENDER", [results_list.xls]Results!M:M, "MALE")

This makes me think that COUNTIFS just don't work with named references..

View 8 Replies
View Related
Apr 28, 2009

Can you use Row & Column numbers in a Formula the way you can in VBA?

I want to do the same as Range(Cells(5,2)) in VBA EXCEPT in a Formula

because I want to use named ranges for the Row & column entries. (And I don't want to have to run a macro every time a change is made. The spreadsheet is huge enough already. It's slow on my machine & I have the biggets baddest PC in the company!)

Using Formulas only, (not VBA) I would like to create a Dynamic Named Range, LastUsedRow, which is the ROW NUMBER of the Last Used Cell in Column C

(it would = 470)

Also I have an existing Named Range HeaderRowNum (it = 16)

Currently I have a LOT of formulas like:

=SUMPRODUCT(($E17:$E470)*(--(CO17:CO470>0)))

problem is any new data must be added between Rows 17 & 470

So I would like to create dynamic new forumlas to read like:

SUMPRODUCT( (Cells(HeaderRowNum+1,5) : (LastUsed Row,5)) * (--(Cells(HeaderRowNum+1,93) : (LastUsed Row,93))>0) )...............

View 5 Replies
View Related
Jul 22, 2014

I've normally just dynamically selected a range using the xldown feature but because this row contains formula it goes always down to the bottom of the formula instead of the last cell which isn't blank.

How would i go about generating this dynamic range that stops at the first cell that contains no value (but has a formula)? Perhaps a do while loop which looped down until it hit the first blank and then assigned the cells it had looped through as the range?

View 3 Replies
View Related
Aug 13, 2008

I have a problem creating a circular reference with dynamic ranges. I have searched already but can't find the specifc problem. I have a spreadsheet where I use vba code to insert extra columns. Within the worksheet there are sum calculations at the end of each row. When I add a new column within the table I would like the sum calculation to expand to account for this extra column.

Now, I thought that dynamic ranges would be the way to go but the only way I can see to do it creates a circular reference as the CountA function trys to count the cell with the sum function in it.

View 2 Replies
View Related
Sep 27, 2011

I have a query on retrieving the result from a set of range from other sheet.

Query: In the Sheet1 Column B has to take first two words from the

Column A & lookup the matching results from Sheet 2.

(The search range should be the first two words of each cell)

Result: The complete sentance in the Cells (First Two words) should get placed in the Sheet1 Column B

Example: The Expected result should be as mentioned below. (Blue in color should be the results)

Sheet1A BC1NameResults2abc def fkfeabc def words3ghi jkl kikgN/A4mno pqr stu lkasmno pqr must work5

Don't give my book don't give up6vwx yzyvwx yzy not7xwv uts rqpxwv uts yes8omn lkjN/A9ihg fed cbaN/A

Sheet2A BC1Lookup Data2abc def words3xxxghi jkl4yes you have to5don't give up6no you will not7mno pqr must work8vwx yzy not9Excel work10xwv uts yes11omn mre lkj12ihg not fed 13not work14

This is my query

View 7 Replies
View Related
Jun 22, 2012

I've am array formula that I use to provide highest values:

Code:

{=INDEX(Data!$D$2:$D$128, MIN(IF(LARGE(Data!$D$2:$D$128,ROW(A1))

=Data!$D$2:$D$128, ROW(Data!$D$2:$D$128)-MIN(ROW(Data!$D$2:$D$128))+1)),

MATCH(LARGE(Data!$D$2:$D$128,ROW(A1)), INDEX(Data!$D$2:$D$128,

MIN(IF(LARGE(Data!$D$2:$D$128,ROW(A1))=Data!$D$2:$D$128, ROW(Data!$D$2:$D$128)-

MIN(ROW(Data!$D$2:$D$128))+1)), , 1), 0), 1)}

I want to replace the range Data!$D$2:$D$128 with a dynamic reference: like that one:

Code:

ADDRESS(MATCH(Summary!$B$1,Data!$Q$2:$Q$10000,0)+1,

MATCH(Summary!$B$7,Data!1:1,0),1,1,"Data")&":"&ADDRESS(

MATCH(Summary!$B$1,Data!$Q$2:$Q$10000,0)+COUNTIF(Data!$Q$2:$Q$10000,Summary!$B$1),

MATCH(Summary!$B$7,Data!1:1,0),1,1,"Data")

The two formulas work well in separate sheets but crash when put together, how could I possibly insert the second address formula into the first one?

View 2 Replies
View Related
Nov 22, 2006

The following formula produces the desired result:

=COUNTIF(INDIRECT(TEXT(A8,"mmm")&"!B2:Z100"),"SK")

but replacing the range of cells with a dynamic named range returns #REF!:

=COUNTIF(INDIRECT(TEXT(A8,"mmm")&"!Data"),"SK")

where A8 is the date 01/01/07. I'm trying to count items within the range Jan!Data.

I'm not sure if I'm trying to do the impossible, or if I'm missing something.

View 9 Replies
View Related
May 29, 2007

I am having a few problems with dynamic named range in excel 2000.

When adding new data to the range, excel extends the range correctly, but only copies some of the formula correctly. It does not copy the formula that references a cell from another line.

I am trying to create a excel spreadsheet and have a formula =e10-e9, which does not copy down.

View 10 Replies
View Related
Dec 11, 2012

I'm trying to create a dynamic COUNTIF function that references a previous cell.

For example, if I have 10 columns:

A B C D E F G H I J

a 1 b 2 c 3 a 1 b 3

I am trying to write a function that will let me reference A1:J1 and then if the cell contains an A, do a countif among the cell+1 for cells that have a value of "1". In this example, that function would return a count of 2. I think that I need some kind of combination of IF, INDIRECT, and COUNTIF, but I'm not sure how to string them all together--or maybe there's another way altogether.

View 5 Replies
View Related
Dec 22, 2009

I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.

Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?

i.e.

First dynamic range: called "Milestones" at A11

Second dynamic range: called "Activities" at A25

& make a Validation list that will list content of both

View 9 Replies
View Related
Apr 3, 2008

Im sorting a dynamic range as mentioned in this Sorting a Named Range. My range is called drWarningTypes and is defined as:

=OFFSET(DataSource!$A$2,0,0, COUNTA(DataSource!$A:$A)-1,1)

When there is only one cell in the range, then running the following sort function includes A1 also in the search (and also adjoining columns).....

View 9 Replies
View Related
Dec 19, 2012

I have a spreadsheet that contains a list of dates: What I need is a formula that will count the number of cells that hava a date more than 6 months old. I also would like the field automated so I don't have to change the date manually every day.

=COUNTIF(S5:S593,"<2012/06/18") This formula will give the correct read out, but I must change the date manually.

My attempts at adding a =today() command in place of the date result in 0 being the result (not correct)

This is what I have tried: =COUNTIF(S5:S593,"<"=TODAY()-"183")

View 8 Replies
View Related
Jul 15, 2014

I have a column of data that keeps getting new information in it. what i need is a named range that i can use for a chart, first point in the named range will be the first value in the column, second point will be avg. of point 1 and 2, then 3rd point will be avg. of 1,2,3 etc.

View 1 Replies
View Related
Oct 28, 2009

I am trying to get to grips with the dynamic ranges for pivot tables. I have named a range data and in the refers to section put:

=OFFSET(Sheet2!A1,0,0,COUNTA(Sheet2!A:A),COUNTA(Sheet2!1:1))

I was hoping that then when i go to data > Pivot table and it asks for the range i could put =Data but it tells me that the range is invalid. I have attached a copy at the bottom.

View 3 Replies
View Related
Feb 3, 2009

- Column D includes dates.

- Column E includes text, either "Yes" or "No".

I want Excel to count all cells in Column D between 1-Jan-2009 to 31-Jan-2009 + any cells that say "Yes" from Column E on the same row.

View 5 Replies
View Related
Oct 16, 2009

Is ther any way around not being able to do this - I read that if u make the ranges an array it shoul work - Shift, Control, Enter - or something but I can get it to work. I was hoping to use copuntif for this :-

COUNTIF(F33:F39,M33:M39,T33:T39,AA33:AA39,AH33:AH39,AO33:AO39,F67:F73,M67:M73,T67:T73,AA67:AA73,AH67 :AH73,"b")

View 5 Replies
View Related
Feb 3, 2009

How do i create a formula for countif with range name

I did create a formula =COUNTIF(C2:C868,"NS") but it show 0

NS range name contain working shift

NS

0:00 - 9:30

7:00 - 16:30

7:30 - 17:00

7:45 - 17:15

8:00 - 17:30

8:15 - 17:45

8:30 - 18:00

View 9 Replies
View Related