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 RepliesI 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] .....
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.
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..
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.
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.
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
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).....
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 RelatedI 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.
- 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.
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")
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
I have a column with multiple data entries ( dates, amounts, percentages, etc).
From these I want to count how many dates are after the selected date.
But I am unable to pickup date cells selectively.
i.e. counif ( {A1, A6, A11, A16, A21, A26}, >=1-Jan-2009)
But the function is giving error as it only accepts ranges.
I can use countif(A1:A26, >=1-Jan-2009)
But the problem is some numerical values are also in the same range ( as the numerical format of dates) - so I am unable to use it.
My searches have not produced anything that I could apply to this situation.
I'm trying to write VBA that would:
1. Search a Workbook for Dynamic Ranges.
2. When a Dynamic Range is found the code would:
A. Determine the current coordinates for the range.
B. Change the "Refers To" value From "=OFFSET...." To "=Worksheet_Name $Column$Row:$Column$Row"
3. Save Changes.
4. Close File.
My apologies but I have very little experience in writing VBA. I understand about variables, arguments, and IF/THEN but just enough to use functions within Excel.
I want to count a range of percentage how many items fall under the % range below:-
from 0% to +- 10% (equal or +-10%)
from +-11% to +-20%
from +- 21% to +-30%
from more than +- 31%
I am trying to solve a a problem that I am having.
I have a list of projects in one column and start dates in a different column.
I want to create a dashboard that breaks down projects by month, quarter and year to date.
I have tried various formulas centered around CountIf. Bottom line is I want to review a column of dates and determine how many projects started last month, etc.....
I want to count all cells over 1000 and then I want to count all cells over 990, but less than 999.
These are the formulas I am using
=COUNTIF(CN3:CU65,">=1000")
=COUNTIF(CN3:CU65,">=990")
In this formula, the over 990 also counts the ones over 1000 and I only want them to count 990 to 999.
I am trying to use the COUNTIF function but over a selectable range. in other words instead of a fixed range like =COUNTIF(A1:A11,"=yes") I want to be able to choose the range Axx:Axx selected by drop down menus.
View 3 Replies View RelatedHow can I countif on a row/range
I tried this but this wont work. All i want to countif there are duplicate rows. This formula will be copied down
=countif($A$1:$A$11&$B$1:$B$11&$C$1:$C$11,A1&B1&C1)
I have a CountIFS formula for a cell range for a project for work
Code: =COUNTIFS($C$75:$C$1999,A71,$G$75:$G$1999,">=-1",$G$75:$G$1999,"
#1
1
2
3
4
5
6
7
8
#2
Red
Yellow
Green
Blue
Violet
[Code] .....
I am trying to determine how many times a value occurs in each quartile in a data set, with the range of my quartiles changing for each row.
Above is a simplified sample of the data I am working with. What I would like to do is calculate the length of a quartile for each row, then determine whether "Red" is in the 1st, 2nd, 3rd, or 4th quartile
Right now I have two output tables. The first is a "Quartile" table, which for each row counts the number of values entered, divides by four, multiplies by the appropriate quartile and rounds down. For this I use the function
=rounddown(counta(range)/4*(quartile))
For Q1 in this dataset, that is =rounddown(counta($B2:$I2)/4*(1))
Then I have a table which calculates whether "Red" occurs in each quartile, with references to the cell values in my quartile table. My formula for Q1 here is
=countif(index(row#2,1,match(Q1 value,$row#1,0)):Index(row#2,1,match(Q1 value,$row#1,0)),"Red".
The actual formula (can be used if pasting table values into excel) is:
=countif(index($B2,$I2,1,match(Q1,$B$1:$I$1,0)):index($B2:$I2,1,match(Q1,$B$1:$I$1,0)),"Red")
Where Q1 equals my rounddown formula noted above, returning "1" in this case. For subsequent quartiles, I change my reference in the Match formula to start with the previous quartile +1, and end with the current quartile.
I want a conditional format change to happen when certain criteria is met.
I have a conditional formula in column F
Let's say F173
If the value in A173 value is found anywhere above cell A173, then make a blue box surrounding cell F173.
I want to be able to copy this conditional format down column F
When I apply the countif function to a named data range, it evaluates to #VALUE. I'm trying to count the number of entries in the range (Belgium.Data) that are strictly greater than zero, to calculate a y.t.d. average that won't be biased by incomplete data. The range-name is well-defined - the arithmetic functions like sum, sumproduct etc work and evaluate correctly across the range, but countif always refuses to return a value. I've also tried
"=COUNT(Belgium.Data>0)"
to no avail, as well as all the following:
"=COUNTIF(Belgium.Data,">"&0)";
"=COUNTIF(Belgium.Data,">0")";
"=SUM(Belgium.Data>0)".
Even the last didn't evaluate, even though "=SUM(Belgium.Data)" evaluates correctly. I can't seem to use the actual cell-references either, since the range is non-contiguous - I miss out every 11th cell to compute an average on the last 11 cells - and Excel doesn't seem to like the comma ("union") operator, insisting the 2nd component of the union is COUNTIF's "criteria" argument.
I am trying to take a large spreadsheet with 2000+ rows of data each with its on unique date associated with each row (its a dump of invoices that need to get paid). I need to count how many invoices are each of the seperate 52 weeks of the last year using I believe a Countif function. My problem is I cannot seem to get the criteria written correctly. For example I want to count out of the 2000+ rows of data, how many invoice dates fall between say, Jan 1 and Jan 7. I would think it would be as easy as using the Countif function and then writing the criteria as a forumula to count invoices that have a date that is greater than or equal to Jan 1 BUT less than or equal to Jan 7. It is that precise formula that is giving me much frustration.
I have a similar Sumif forumula problem as I need to total the $ of the invoices but I think if I get the criteria to work for Countif it will work for Sumif.
I have a range of data that will grow as the days of the month pass but I need to run calculations on this range. The data is 13 columns wide but the number of rows will increase daily. I remember using a formula in the Define Range that would automatically take into account new additions using the OFFSET function but cannot for the life of me think how exactly to do it.
View 4 Replies View RelatedLine of code that will Select a Named Range in this case I have Named a CELL "DataSummary" Need to use that named range by selecting 30 columns and 54 rows.
Range("DataSummary),(??,??) doesn't work.
I have been working on part of the code for my spreadsheet and it works fine in the spreadsheet “Databaseform” however when I copied the code to my master spreadsheet “Paul_PartLocDBCombo” it does not work, I get the error:
Method ‘ range’ of object ‘_worksheet’ failed
The code is then highlighted in yellow, the code is:
Set rng = wksPartsData.Range("a1", Range("a65536").End(xlUp))
Meaning this part is incorrect but I don’t know why? To work it: go to Databaseform and press start. Enter 7mm in the product field and press find all. It will then return all the matching results in the userform. Its this I want to try and achieve on the other spreadsheet when the button find label is pressed.
I have a start date column and column for status. Status is either "in-progress" or "complete". I want to count the number of "complete" items that fall within a specific date range. I have tried countif but I can only seem to get a count of all items that fall between the date range. I do not want a pivot table for this.
View 3 Replies View RelatedIn column A I have a list if places that can contain duplicates ie
Manchester
Birmingham
London
Birmingham
London
Manchester
Manchester
London
In column B through to D a list of statements to which there are multiple answers i.e.
Yes / Maybe / No
What I'd like to know is how many 'Yes' answers are in the data range for column B:D in Manchester
I've used a countifs but have to result to multiple countifs adding each column together which is fine for 3 columns but not when there are 50!