Countif With Dynamic Ranges
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
ADVERTISEMENT
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
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
Jun 21, 2009
I would like to replace the blue bit of this Dynamic Named Range (DNR) with an INDIRECT formula in order to modify the starting point for the DNR: =OFFSET(DATA!$C$60,0,0,20,1). Unfortunately, I can’t seem to get my INDIRECT formula to work in order to use it to replace the blue bit above: INDIRECT("'"DATA"'!&ADDRESS(MATCH('SHEET1'!AC8,DATA_Date,0)+22,3)"). The orange bit of the formula above returns a value of 60, therefore the ADDRESS formula should return $C$60, that can then feed into the INDIRECT and act as the equivalent of DATA!$C$60. But it doesn’t.
View 2 Replies
View Related
Feb 26, 2010
definitions for static ranges and dynamic ranges?
View 9 Replies
View Related
Aug 28, 2008
I have 2 ranges with values, and I want to use countif to see of there's a same value in each of these ranges.
View 9 Replies
View Related
May 14, 2007
I'm attempting to use the countif formula to make sure a number is entered only once in 4 nonconnected ranges, I492:I496, I499:I503, K492:K496 and K499:K503. I've tried everything I know and as I'm self taught and my teacher is an idiot, I'm not having any luck.
View 14 Replies
View Related
Jan 20, 2010
Countif: Is there a way to have a single criteria (a persons name) and multiple ranges example: a6:a10 c6:c10 and recieve the sum of that criteria and ranges?? I know there is I just cant get it.
I need to count a persons name entered in multiple ranges (cells or areas) on the same worksheet. I cannot make one big range because i will need to do the same for the b6:b10 but for a different "need" the a colum and c colum bieng "completed" the b colum bieng "not completed". I have tried =countif(a6:a10 + c6:c10, cell_with _persons_name) and for obvious reasons that wont work,
View 5 Replies
View Related
Sep 29, 2011
Is there a more efficient way to collect information from multiple ranges than using the COUNTIF and adding them together, such as
COUNTIF(Range,Criteria)+COUNTIF(Range,Criteria)+COUNTIF(Range,Criteria), etc?
I know my way around Excel, but I really do not understand much about VBA
View 1 Replies
View Related
Jan 10, 2008
I am using a work sheet where I want to count if Column A has one criteria and column B has another
example
column B states is used to track contracts it can be vendor
column L tracks status it can be open, received, or closed
I want to count the cell if the vendor name matches and the status is open
also if that is possible is the same possible with 3 ranges and criterias?
View 9 Replies
View Related
Oct 27, 2009
i had some formula provided by this site which seemed to work but is now resulting in #N/A or a zero value - can you take a look?
=SUMPRODUCT(('Report X'!$J$6:$J$10000="2")*('Report X'!$E$6:$E$10000>TODAY()-30))
Report X contains 2 relevant columns... E contains the date created for each item, J contains the type of each item - in this case i wish to see how many items '2' there are in the last 30 days
Report X is a daily report copied into the same tab each day - so the data will change every day depending how long each item is still in the system
this formula does work in the example sheet i provided but not in the spreadsheet in which i need it to work!
i also have similar formulas to calculate how many items are present in between 30 and 60 days exist, and how many over 60 days - all return the value "0"
column E containing dates is in the format 01/10/2008 - changed using Ctrl F - replace to amend it from 01.10.2008
View 9 Replies
View Related
Oct 30, 2009
How to use COUNTIF when there are multiple criteria. For eg. I have 3 columns, and I want to count the # of employees in each row if all 3 criteria in columns C, D, and E hold true.
View 9 Replies
View Related
Feb 22, 2008
I'm creating a coversheet that shows the percentage of "yes" and "no" answers from several cells in other sheets of the book. My problem is that I'm trying to use a countif statement to do it, and the cells i'm counting aren't in a simple range. ex. A1:A10
In stead i'm trying to get something like:
=COUNTIF((Sheet2!A2,Sheet2!A10,Sheet2!A15),"yes")
View 2 Replies
View Related
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 16, 2008
I want excel to count the number of items in a range that I have named "Name" and I have another range that I have name "date" which contains (obviously) dates. The dates are in order. I want to count the number of items in "name" that are associated with the date in the "Date" range.
The problem is I want to count the names in a date range, which is todays date through to 30 days after. I have to days date already posted automatically in K1 [by the formula =TODAY()]
Essentially, count all the times "Bob" appears in the Range "Names" that appear in the next 30 days. My Brain hurts just trying to describe it
View 9 Replies
View Related
Oct 7, 2006
I have cells containing data within C15:C22
I also have cells containing data within E25:E32
Some of these cells have the value '5' in them.
I want to have a running total of all the cells in these two ranges that have a value of '5' in them.
I did this formula:
=COUNTIF($C$15:$C$22,"=5")
This works okay but unfortunately this only covers the first data range. How do I specify the other data range in this formula?
View 5 Replies
View Related
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
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
Jul 2, 2009
My problem is :
1.In G Column I put logic for Fail and Obtained Marks.
G2=IF(COUNTIF(B2:F2,">=60")=5,SUM(B2:F2),"Fail")
2. Now in H column I want use this formula which I obtained from this forum
H2=SUMPRODUCT((G$2:G$7>G2)/COUNTIF(G$2:G$7,G$2:G$7&""))+1
To get the position of Students.
But the text value "fail" in the G2:G7 getting Position No. 1 and i've noticed the reason by using evaluate formula as well.
3. I got solution by changing "Fail" with 0 by creating column I and then column H put this formula ........
View 14 Replies
View Related
Mar 19, 2009
I am having trouble writing a COUNTIF formula that will tell me how many times a particular month appears in a list of dates. January may appear 10 times, February may appear 43 times, March 102 times, etc. Instead of manually selecting the range for each month and using the count feature on the status bar, I need a formula that will count for me.
I tried:
COUNTIF(B:B,month(1))
COUNTIF(B:B,month=1)
as well as several other lame attempts, but kept getting a result of zero or an error.
View 9 Replies
View Related
Jul 6, 2009
I want to get students positions in a class like 1st, 2nd, 3rd, etc. There can be two 1st position and 2 2nd positions at a time .
1.In G Column I put logic for Fail and Obtained Marks.
G2=IF(COUNTIF(B2:F2,">=60")=5,SUM(B2:F2),"Fail")
2. Then i made array formula in column H to get the position
={SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1}
Some improvement to write fail & position
={IF(COUNTIF(B3:F3,">=60")5,"Fail",SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G3))/COUNTIF(G$2:G$7,G$2:G$7&""))+1)}
And more work to get total marks & position and Fail at the same time......
={IF(COUNTIF(B2:F2,">=60")5,"Fail",SUM(B2:F2)&"-"&SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1)}
Now I wounder that is there a way to do all calculations in column H without using column G
By replacing (if there is any method) G2, G3, G4 etc. with Sum(B2:F2); Sum(B3:F3);Sum(B4:F4) etc. in below formula
={SUMPRODUCT((IF(ISNUMBER($G$2:$G$7),$G$2:$G$7,0)>N(G2))/COUNTIF(G$2:G$7,G$2:G$7&""))+1}.....................
View 9 Replies
View Related
May 8, 2012
I am using Excel 2010. I have a spreadsheet where column A is for Quarter, column B is for Employee, and columns C-R are for Codes (Code1; Code2; Code3; etc. through Code15). One line might have no values in the codes columns, another might have values in only Code1, another might have values in Code1 and Code2, and another might have values in all 15 columns.
A B C D E F --> R
1 Qtr Emp Code01 Code02 Code03 Code04 --> to Code15
2 2012.1 Liz CER02 INS12 WKH15
3 2012.2 Jim PIN55 WKH12 WKH19 WKH23
4 2012.2 Jon
5 2012.2 Jim WKH15 WKH23
6 2012.2 Jon PIN55 WKH15 WKH12 CER08
The worksheet is named "ALLAUDITS" and the named ranges are as follows:
Quarter: =offset(allaudits!$A$1,0,0,counta(allaudits!$A:$A),1)
Employee: =offset(allaudits!$B$1,0,0,counta(allaudits!$B:$B),1)
CodeData: =offset(allaudits!$C$1,0,0,counta(allaudits!$C:$C),15)
On another worksheet I need to be able to count how many times WKH15 appears in the CodeData range within a certain Quarter. I tried
=countifs(Quarter,"2012.2",CodeData,"WKH15")
But that didn't work, and from what I can tell in Excel support, it's because the ranges are different sizes. I have over 6500 rows, so I'm trying to avoid having to name all 15 columns and then do a sumproduct on all of them, such as
=sumproduct((Quarter="2012.2")*((Code1="WKH15")+(Code2="WKH15")+.......)))
Calculation would take forever.
I could just create a pivot table based on the "allaudits" worksheet, but I can't figure out how to get the actual codes as the row headers (instead of Code1, Code2, Code3, etc.).
View 3 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
Oct 28, 2008
I want to create dependent lists using data validation. The lists need to be created from ranges that will be growing as users add more data. I think the best way to create the list is a VLOOKUP. However, I am not sure how to use the VLOOKUP when the range is changing.
I have attached an example. I have a list on sheet "Vlookup" called "FRUIT" with "apple", "orange", "banana". Then to make the depedent lists I have created three other lists called: "APPLE", "ORANGE", "BANANA". I want to pull the COST from sheet "VALUES" into the lists "APPLE", "ORANGE", "BANANA". Users will be adding costs next to the FRUIT they purchase.
View 5 Replies
View Related
Nov 18, 2009
I have a particular issue - i am trying to create a rolling correlation.
lets say you have two variables for 3 different months:
Month1 a x
Month2 b y
Month3 c z
correlation will correlate a-c with t-v
however, come next month - i have to manually drag the ranges for a rolling three month correlation:
Month1 a t
Month2 b u
Month3 c v
Month4 d w
correlation will now correlate b-d with u-w for a the last three months.
I want to automate this as I know what the month is, I can't seem to return the last three months and correlate the data.
I have tried to do this with the database functions, but I think that they break the array relationship of b-d and u-w in the correlation function.
View 9 Replies
View Related
Jan 20, 2007
In the example I have attached, I need 4 named, dynamic ranges to build a chart with:
1) Dates for the chart
always starting from A2 ... to whenever the LAST sum of B, C, D =0
(right now that would be A2:A30)
2) The Amounts
I need the amounts as 3 individual dynamic ranges, with no 0 values
(example - B2:B21, or c10:c14, etc)
3)The date column
same as #2, but just a single cell reference to show as a marker on the chart.
(example e21:e21)
I know someone here will think 'so what's the big deal' and pop it out in 30 seconds.
View 10 Replies
View Related
Oct 6, 2008
I'm trying to make ranges in a SumProduct formula dynamic, but getting errors #N/A. I think this is because the top two rows are headers, throwing off the range count.
Q. How do I adjust the range definition to compensate., e.g.
Range =Offset(Sheet1!$D$3,0,0,COUNT(Sheet1!$D:$D),1) where column D is numeric, and
Range = Offset(Sheet1!$T$3,0,0,Match("*",Sheet1!$T:$T,-1),1) where column T is text?
I had assumed that the offset value 3 would do this, but I suspect the functions count and match are not doing so.
View 8 Replies
View Related
Feb 24, 2012
I am trying to create a vlookup to get a count of trouble tickets techs completed daily within a table I created, I am using a table since its dynamic. For example I need to see how many tickets Joe completed in a day. See below...
Tech Ticket# Comments Status
Joe 1234 Replaced HD Closed
John 3212 Replace Motherboard Closed
Joe 5678 Installed OS Closed
Above is just an example (my table has 40 columns). I need to vlookup joe and get the count of the closed tickets.
View 6 Replies
View Related
Mar 25, 2014
So I was looking for reassurance or validation more than anything. From what I can tell you can in order to build a chart that is dynamic throughout a range, you use the offset and count or counta function - 1. That part isn't a problem. My question is once you created that for your charts do you just normally plot your chart range or do you have to reference the named range directly into the chart range?
View 2 Replies
View Related
May 21, 2014
I have monthly sales, and i wanna be able to sum the last-twelve month sales, when i select the month from a drop-down list. For example, when i select Feb14 from the drop-down, I want the formula to sum the sales between mar13-feb14. how can i accomplish that?
Select Month
Feb14
LTM Sales
?
[Code] ..........
View 7 Replies
View Related
