Countif With Multiple Ranges
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
ADVERTISEMENT
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
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
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
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
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
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
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
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
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
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
Apr 21, 2013
I've got several worksheets that all have the exact same layout that a user will enter unique information in to each worksheet. Then I've got a final worksheet that I want to have a button that the user can click and when they do, it will look to each worksheet and do the exact same process for each worksheet as follows:
It first looks to see if the worksheet is visible. If it is, I want it to copy the range A5 to K5 down until it gets to the last non-blank cell in column C. The first non blank cell that will be referenced will be C7. Then I want it to paste this information into the range A5:K5 on the final sheet named Sheet8 with the same values and keep cell formatting such as width and height, font. If the worksheet is not visible, it skips the sheet.
I want it to do this for each visible worksheet, placing the next visible worksheet info under the previous visible worksheet info. My current code as shown doesn't do that. It requires that something be inSheet8 A6 before it will even paste, then it pastes the info from A5:K5 but it doesn't do just the values nor does it keep the formatting. What I mean about not doing just the values is some of the info that needs to be copied comes from a drop down they can choose from and it copies the actual drop down menu. Also, it seems to copy all of the ranges from each sheet and paste it into just A5:K5 on Sheet8 and overwrites each other instead of pasting Sheet2 just below the information from Sheet1. So the only information shown after the entire process is completed is the information from the last visible sheet.
If Worksheets("Sheet1").Visible = True Then
Sheets("Sheet1").Range(Sheets("Sheet1").Range("A5:K5"),
Sheets("Sheet1").Range("C7").End(xlDown)).Copy
Sheets("Sheet8").Range("A5").End(xlDown)
End If
[Code]...
View 4 Replies
View Related
Jun 16, 2007
I inherited a spreadsheet that had an userform where the user checked off which 'pages' he wanted to print. The Ok button routine used if statements to run a routine for each 'page.' Here's an example of the original code for one page:
Sub Button2_Click()
Sheet7.Activate
Run "HorizontalPrintStuff" 'generic landscape pagesetup
With ActiveSheet.PageSetup 'specific pageset settings
.RightFooter = " Construction Assumptions"
.PrintArea = "CONSTRUCTION" 'the named range to print
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1 'this changes depending upon the page selected
End With
ActiveSheet.PrintOut
End Sub
The problem was it printed each page as a separate print job; and if you print to adobe, you get serveral files, not one file. That and it took a long time to run.
So I tried a different tack. If the checkboxes has true, then the printarea is set to that named range. If there were more than one named range on a sheet to be printed, I consolidated them. I did this with a bunch of if statements - very cumbersome.
Sheet3.Activate
'Sheet3.ResetAllPageBreaks 'disabled due to errors
Run "HorizontalPrintStuff" 'generic landscape pagesetup
With ActiveSheet.PageSetup 'specific pageset settings
.PrintArea = "DEVBGTALL" 'the named range to print
.FitToPagesWide = 4 'this changes depending upon the
.FitToPagesTall = 1
End With
I haven't shown all the code cause it goes on for 12 sheets containing 16 different printareas.
My current muck ups are .....
1) it prints every printarea/named range on a given sheet (I took out all the if statements trying to debug everything.) Is there another conditional argument that allows for multiple 'trues'?
2) the pagebreaks in printarea/named ranges that are multiple pages (like a 48 month schedule) won't stay set. I've tried both VPageBreaks(3).Location:= and .VPageBreaks.Add Before:=
3) the Sheet1.select false argument is always adding a random sheet to the end of the print job. Don't know why.
I can do all this in a recorded macro, just not the selection userform. I've thought about copying to another sheet or hiding columns and rows then printing, but that seems just as cumbersome.
To recap, i want to print out, as one print job, multiple printareas from mulitple sheets, based upon checkbox selection on an userform.
View 6 Replies
View Related
Jul 6, 2007
I need to be able to track changes on selected ranges on multiple sheets, but Excel does not appear to be able to do this. It only appears to allow me to select multiple ranges on the same sheet.
is there a way to track changes on multiple selected ranges on multiple sheets
View 9 Replies
View Related
Jun 15, 2008
In the attached file i have multiple tables for different types of conservatory roofs (16 of them in total). The ranges at the top and side relate to milimeter measurements and the data in the middle relate to the price for that sized conservatory roof. The table works where the two ranges intercept each other. I have a formula to do this for one of the tables only. What i would like is a way of choosing which type of roof to use (i.e. which table to use) and then to be able to input the measurements and the price to be displayed. All of this needs to be done in one query so its as user friendly as possible. i've had is to use a pivot table, i feel it is not possible to use a pivot table to do this sort if thing after research into them, although i am un-familiar in the making of them
View 4 Replies
View Related
Feb 21, 2013
Im trying to make a vlookup so I can say find a record where column A is >4, B is =2, C is 1.
Example - The lookup would find these 2 ranges.
7
2
3
2
6
4
[code]....
View 1 Replies
View Related
Sep 24, 2008
Following on from: http://www.excelforum.com/excel-misc...s-formula.html
I used the above formula to work out how many days a call went out of our SLA, that works great (puts the values into K)
View 4 Replies
View Related
Oct 8, 2008
I am trying to create a formula that will tell me how often our sales reps are going to specific accounts. I have a master list of each persons accounts and next to each account name there will be a cell with a formula in it. This formula will look at the cell next to it, "the account name", and then count how many times that name comes up over each months sheet. I was also wondering if I can then get the average over 12 months in the same formula. If not thats ok.
The problem I am having is that I keep getting an error #VALUE!. I had no problem with it looking at only one sheet, but when I do more than one it gives me an error. I think it is the was I am breaking up each sheet. I have tried to do it every way I can think of... ,;) Nothing seems to work.
View 4 Replies
View Related
Nov 16, 2008
I have a table like this:
Name (A).....Date-in (B)....Date-out (C)
Name1........27/12/2008....3/01/2009
Name2........25/11/2008....28/11/2008
...
I want to count the rows (in the entire table) that B and C dates intersect with a reference dates (say J1 and J2).
It is a booking table so I want to know if the apartment is available for the reference dates (i.e. no bookings for that days).
I tried first a simple double conditional to know if a date is inside two dates but it didn't work:
=SUMPRODUCT(--(B:B<J1),--(C:C>J1))
View 9 Replies
View Related
Mar 8, 2009
I have a workbook with alot of sheets, each sheet is identical in format. What I am trying to achive is a way of counting all the occurances of a name in a range of cells on all sheets.
To try and explain, each sheet has a drop down list in cells C5:V5, and I need to try and get a way of a summary sheet showing how many times "J.Bloggs" appears in All Sheets accorss the range C5:V5, but am finding it impossible.
View 5 Replies
View Related
Mar 19, 2009
I am trying to tally answers for a survey. Column A specifies one of 3 locations (Boulder, Larimer, Westminster) and column C specifies a grade for services between locations (Not Uniform, Slightly Uniform, Very Uniform). I was able to tally each separately using 'COUNTIF' and the conditions, but now I would like a total for each of the grades by location, i.e. a count of people answering both Boulder and Uniform, etc.
View 5 Replies
View Related
Apr 14, 2009
In my Sheet "List" I have list of persons working on different projects.
I prepared graph after putting conditions on Project Type, Project Size, Project Year & Position (PM Project Manger). Every thing was done a in a nice manner with the help of below formula.
PJ TYPE , PJSIZE, PJYEAR, POSITION are ranges names.
=SUMPRODUCT(--(PJTYPE=$A$2),--(PJSIZE=$A6),--(PJYEAR=B$4),--(POSITION="PM"))
But the problem was occured that in a year if a person work on small project more than once then he will be counted only once. But if he has worked in same year on Medium or Larage project then they will be counted separately. I tried to oversome the problem with the help of Pivot Table and put manually some legend P1, P2 & P3 against the person name if he is working on same type of project in same year. then count only P1 in my formula to count how many Project Manager worked on Project. like
=SUMPRODUCT(--(PJTYPE=$A$2),--(PJSIZE=$A6),--(PJYEAR=B$4),--(POSITION="PM"),--(PMCOUNT="P1"))
Now i am trying that in a separate columm of # of PM there must be a formula which only put P or 1 for a person if he is working on same project in a year but i want that p or 1 only appear against his first entry i duplicate. for other persons it automatically enter 1 or p if they are appearing only once. i have tried a lot while using countif with multiple conditions but all in vain.
View 3 Replies
View Related
Jan 9, 2010
i want to use Countif() when 3 ceritera are true
ceritera
--------
1) Branch Name
2) Status
3) Login Date
can this be done by any mean
View 14 Replies
View Related
Feb 12, 2010
what I'm trying to do is to make a logbook for a machining center. Each part has an op10 and an op20, essentially front and back. And each part number falls into the category of OS or FS. I've used AND logic to make tables in hidden columns to be used by a countif statement to determine my totals. I.e. to determine if a scroll is completed, op20 has a a value of 1 AND column C is "OS".
I use
=IF(AND(A9=1;C9="OS");1;" ")
Then I countif criteria is 1 in the column i created with that statement.
That works just fine. Now what I want to do is to be able to create daily totals of OS and FS by simply modifying a variable date in a formula. So I'd like to essentially say: Countif Column C =OS and Corresponding column D = 1, and corresponding Shift date = 10.02.12(date to be variable). I'm at a wall here. Is there any way to do this somewhat simply?
View 5 Replies
View Related
Nov 11, 2007
I have 50 worksheets with shirt sizes. I am trying to count the number of instances that we have "XL".
So I use the formula:
=COUNTIF(worksheet1!A1:worksheet50!A1, "XL")
I also tried:
=COUNTIF(worksheet1!A1, "XL")+COUNTIF(Worksheet2!A1, "XL")+COUNTIF(worksheet3!A1, "XL")...etc
Niether work, in fact, excel decides just to leave the function as written above in the cell when I enter out of it or go to another cell. Sometimes it works if I "COUNTIF" multiple cells in one worksheet but will leave the formula as is if I try to manipilate it.
View 9 Replies
View Related
