what I'm looking at doing is counting the number of reccuring unauthorised absences in a list. We have the persons name in column B, the absence type in column C and the date in column E. Basically I need to send out an AWOL notice when 5 days of unauthorised absence for the person in column B has occurred, so I need some sort of indication that this has happened in order for me to stick some conditional formatting in there to flag it.
I have a sheet with data which is all sorted by columns.
What I want to be able to count the occurrence of an instance when 2 cells in the same row (but in different columns) match my given criteria.
Ive been using 'countif' to count the instances in a single column and I am now aware that this is about all it can be used for.
An example of my data is:
A B 1 a 1 a 1 c 2 d 2 c 3 b 3 b 3 c 3 d
What I want to be able to count for example is how many times '3' & 'b' occur on the same row (in this case the answer would be 2).
Im not very good with pivot tables and have tried but have not managed to find an answer. I really hope that someone can help as Ive been working on this probelm for days and quite frankly its doing my head in :-)
I have a row that contains a different time (hh:mm) in each column where each column represents a different date which is display in row 3. If there is no time the cell is populated with "N/A"
I want to count how many cells for a specified Month/year are not equal to "N/A". I have been able get each selction criteria to work but when I try merging numeric and non-numeric queries I lose it.
The following formula was able to give me the count of cells "N/A" =COUNTIF('Master Data'!$UA59:$ALZ59, ">0")
and this gave me a count by Month/Year =SUMPRODUCT(--(YEAR('Master Data'!$UA3:$ALZ3)=2012),--(MONTH('Master Data'!$UA3:$ALZ3)=1))
When I tried creating one COUNTIF I was unable to get it without an error as I needed the YEAR/MONTH functions.
The columns are: Item, Qty, Date. I need to find out for each item; what is the most common issue qty and how many times that occurred and what was the largets issue qty and how many times that occurs.
I have a highly conveluted way of finding this out but theremust be a quicker way.
I have a slight problem...I have a range of values..
0113 1240 8430 0903
I need to count the occurance of a a particular no. from 0-9.. So i want to know how many times say 0 appears in that range of values etc so on till 9. I tried using CountIF but the problem i face is lets say in my cell 0903 there are 2 0s inside, it doesnt count this 2 zero..
We've got a column of names. A single name might appear once or two hundred times. The column is over 25,000 lines long, so we don't want to have to count how many different users there are (besides the likelihood of doing it wrong!). Is there some way to make Excel count the number of unique entries in the list, ignoring multiple repetitions of the same name?
I have two columns with values of either 1 or -1. I'm trying to find a formula that I can write in one cell that counts the # of times column A and column B have opposite signs.
I have a spreadsheet which consists of Sheet 1 and Sheet 2.
In Sheet 1, Column I there is a list of country names which indicate who visited our website at a certain time. So for example England may be listed multiple times.
In Sheet 2 I have a table showing January through to December in a column and the different country names across the top in one row.
I want the Table in Sheet 2 to count how many times England (for example) occurs in Column I and to indicate that as a numeric value.
I am running Excel 2007 at work in compatibility mode. My company is in the process of switching to Excel 2007, but not everyone has it, so I save my files in Excel 97-2003 format, so they can be viewed by everyone.
Problem: I am trying to count the number of occurrences of "dates" between a starting date and an ending date. (e.g., cells B16:B1000 contain dates when a test at work was performed. I want to count all tests that occurred between 12/30/2007 and 1/9/2007.) Please note that the dates cross the new year
More Info: I have searched this board, found a similar post, and tried the different formulas listed but get a result of "0" (zero). If I try the same formulas at home using Excel 2000, they work. The formula types I've tried include regular and array styles I am led to believe that this is either an Excel 2007 bug (bug??? - Nah, not from Microsoft!), or one of the cute changes between the new and old versions that I keep running across. The last possibility is that I am doing something wrong, which a smart betting man wouldn't rule out either!
I'm working with a set of text values. The sets of text can be either 2 or 3 chars long (in this example 2), and are separated with a pipe. The end char for each set of chars in fixed. In this case a Z.
I'm looking for a formula which counts the unique / distict number of sets of chars. I have been trying to use COUNTIF with a wildcard (?Z), but unfortunately I haven't been able to find the correct formula yet.
I want to count the number of times a certain value is found in columnB, only for unique values in columnA.
I have made a simple example file, attached. Column B shows Status which can be Active, Current, Dormant. Column A holds Item names. I want to know how many Active Items I have, but an Item can appear more than once in ColA, so I want it only to include in the count the unique ColA values.
I am using Excel 2010, and trying to count the number of times a certain name occurs within a specified date period. The date range start date is manually entered in F7, and the date range end date is manually entered in F8. I need to be able to choose different reporting periods. The answer to this question goes into F3.
I also want to know how many working days (Column D) it takes per name. A working day is Monday-Friday, less public holidays. In this period there have been public holidays from 24 December until 2 January (inclusive), 20 January, 6 February, 18 April, 21 April, and 25 April. I put these dates manually into the worksheet "Holidays".
For the number of times a certain name occurs I tried the COUNTIFS function but keep getting a "zero" value. I used the NETWORKINGDAYS function for the working days and have incorporated the holiday periods. That seems to work OK and I can live with the negative date values showing here.
The really tricky part for me - can I combine the COUNTIFS (assuming it works) and NETWORKINGDAYS functions into a single function to only count the working days of a specific name. As some names have yet to be completed (i.e. there is no data in Column C) how do I not count those? The answer to this question goes into F4.
There will be about 6 of these spreadsheets, and I intend to link all worksheet data into a "master" spreadsheet which automatically updates whenever it is opened. The separate spreadsheets are stored in a document management system rather than a network drive so I can't state a filepath.
I am working on a big project involving baseball statistics, evaluating individual seasons. After a great deal of entertaining data gathering, I have a spreadsheet of 1896 seasons that meet certain criteria--that is, seasons belong to an individual player. The player's names are in column A of my spreadsheet.
Individual names appear between 1 and 15 times. I would very much like to generate a quick table (or just a couple of columns) that lists every name in column A, and how many times it appears. My understanding is that the histogram function won't work with text. I'm running Excel 2013 on windows.
I have an excel sheet which is currently 1,100 lines long and expected to increase and has a number of dates in columns W-AO representing the dates in which someone has had an onsite assessment. I wish to report in column AQ if there has been any visit activity in the month, i.e August for this month’s report. Is there an Excel function which will allow for this? Im thinking something like having a drop down list to select the month at the top of the column and then the sheet returns a value of 1 for all lines where a there is a date equal to the month selected?
I guess it is also worth mentioning that the sheet is setup as a table.
I'm looking for the easiest way to count the number of occurrences within a cell range.
The formula that I'm currently using is:
=COUNTIF(D$5:D$8,"a*")
This counts the number of cells that start with 'a' and returns the sum. It seems to work fine, but when I try to make it look for more values in the range it gives me an error. For example;
When I want to find multiple values in the range and count them all, I use this formula:
I am using Excel 2003 and I need to count occurrences involving two colums like this: on sheet "SALES", column I has values ranging from 8.00 to 10.00; column M has positive and negative values.
formula to count how many numbers in column I exist between 8.00 and 8.99 associated to a positive value in column M? And likewise for negatives?
I'm using Excel 2003 and I need to calculate the number of occurrences on according to different criteria in two separate colums.
I am on Sheet 3 and the data I am analyzing is on Sheet 1, titled "RATES". I wish to know how many occurrences are when the word "GB" appears on column B (cells B2 to B100) along with a value greater than zero on column M (cells M2 to M100). The word "GB" does not appear alone but is part of a string of text with different words, I think this is relevant.
I have a spreadsheet for tracking jobs. Most everything is based off of week # rather than date. I am trying to get the stats page of the workbook to tally the total number of late jobs per week.The current week is taken care of because there is a function that automatically displays on time yes or no and I just set it to count the yeses or nos.The problem I am having is for past weeks.
I tried- =COUNTIF(Table2[On-time],"No"+(CountIF(Table2[Week # Hidden],"<Weeknum(Now())" but that doesn't work. I also tried isolating the < like this. =COUNTIF(Table2[On-time],"No"+(CountIF(Table2[Week # Hidden],"<"Weeknum(Now()) and that did not work either.
------ UPDATE
In response to using CountIFS I have also tried-
=COUNTIFS(Table2[On-time],"NO",Table2[Due Week '# Hidden],"<Weeknum(Now())") this just returns a zero value even when I have a late job listed three weeks ago.
I am retrieving a CSV file from the net. In this file there are 'x' amount of row data and 7 columns. I only care about the values in the 7th column for each row. I also don't care about the entire first row. A graphical version would be represented something like this, with the values I want colored in orange:
I've managed to dice this thing into a jagged array by first splitting it using vbLf as a delimiter, and therefore adding those to an array called Lines(). Then I split Lines() up using commas as the delimiter and threw those into a jagged array, let's call it Breadcrumbs()(). I want to throw all the values from Breadcrumbs(i)(6) into an array of its own. Here's my code so far:
Code: Public Sub CSVparser(file As String) Dim Lines As Variant Dim j As Integer Lines = Split(file, vbLf) ReDim breadCrumbs(UBound(Lines)) As Variant For i = 1 to UBound(Lines) - 1 breadCrumbs(i) = Split(Lines(i), ",") Next i End Sub
I have two column on two seperate worksheets. One worksheet has both columns filled out. On the second worksheet, I want to be able to type in info in once column and have excel pull the other column from the other worksheet.
WKSHT 1 WKSHT 2
ID Name ID Name 1 Bill 3 ?VLookup? 2 Steve 3 Mike
Ive tried using Vlookup('worksheet1'!A2:A4,'worksheet1'!B2:B4,2) but can not get it to work.
I have attached a sample from aspeadsheet I am working on. The option reply sheet has '1' entered if the student has opted to take this course. On the other subject sheets I would like the names who have opted for that subject to appear. Stuidents can opted for multiple subjects. I have tried Match and Index functions but it is difficult as students can opt for more than one.
I'm trying to create a template that will be able to return a sales persons call target based on the category they sell and the current level they are. It works when I only ask it to look for one or the other, but I'm getting stuck trying to make it use both. I've attached a small sample. My original equation is as follows
and this works perfectly fine. The problem I've now got is I need to add in a second criteria for setting the row_num. I've tried the following but I get a ref error. =INDEX($C$2:$Q$51,AND(MATCH($B3,$A$2:$A$51,0),MATCH(D3,$B$2:$B$51,0),MATCH(G$1,Telesales!$C$1:$Q$1,0)))
I have a procedure that I want to pass two arrays to. When I pass a single array it works fine but as soon as I try to pass two arrays I get a syntax error. Both arrays are declared exactly the same way and are used in the same way. Am I limited to passing only one array per procedure?
