Excel 2010 :: Filtering Dates And Counting Occurrences?
May 28, 2014
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'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 with 6 months of data in one row. the dates are from A3 (Jan 1) to FY3 (Jun 30). The data is in range A4:FY4. The data is a "$" (without quotes). The $'s are entered randomly during the 6 month period. I need a formula to determine the days between the last occurrence and the next to last occurrence of the $. Example spreadsheet shows the $'s on Mar 28 and May 5 as the last two. The spreadsheet is attached. I am using Microsoft Office Excel 2010.
I have data arranged in a worksheet (see attachment) that has hours of work broken down by day. What I need is a formula that will find the number of times a record occurred in Column F that is greater than or equal to 12 hours each day. So for March 1st there would be 9 times. I can do that now with no problem using "=COUNTIF(F4:F14,">=12")" However, the real thing that I need is how many days of each month were there only 1 count (of 12 hours or more). So it needs to look at the range of data that goes from 3/1/13 to 3/31/13 and find the total number of days that had 1 count (of 12 hours or more) each day and return the number of days it found.
I'm trying to filter a data set to see only those groups that have a subtotal count of 3 or greater. The subtotals are at each change in customer to count the number of divisions that customer is associated with.
In other words, how can I see a list of only those customers who have the permission to shop 3 or more brands?
I have a pivot table in 2010- is there a way to filter the data using an external reference from the pivot table? I'd like to put the value in another cell and have the pivot update automatically when I type a new value in that cell.
I'm trying to make a simple chart, which maps the value of an investment fund over time. I wanted to use the new 'Table' feature within Excel 2010 to format and maintain the formulae within the Table, and the 'Header' for the table contains the date, which is not at regular intervals.
If I opt not to use the Table feature, I can create a line-chart with ease, and Excel recognises that the Dates are indeed dates and plots the graph correctly. The minute I convert over to a Table, the Date headers are no longer recognised as dates, and are instead plotted as if they were text, at regular intervals.
I've tried multiplying the Date Headers by 1 to force them back to true Dates, but this still does not work. I've also changed the setting on the horizontal axis to Date axis rather than automatic, but still no joy.
find the attached excel 2010 file <Count LBP or UBP.xlsx>.
My requirement is if `LBO' is available (in Column A) then the formula should count the number of `LBP' available above the `LBO' upto the blank cell. If there is no `LBP' above the `LBO' then it should plot zero in Correponding Column B Cell. If 2 `LBP's available above the `LBO' upto balnk space it should plot `2' in Correponding Column B Cell.
As like `LBO' and `LBP' the formula should work for `UBO' & `UBP'. refer the attached excel file.
I am copying a large table of data from a report generated in Firefox and pasting it into Excel 2010. The data has several columns of html checkboxes. I need to do two things with the checkboxes and would like to do a third:
1: Count how many checkboxes are ticked in each of the columns. 2: Compare a column A of checkboxes to a column B containing numbers, and then both count and highlight any row where the checkbox is ticked but column B is a 0. 3: (optional) I would like to erase the html checkboxes and, if the box was checked, replace it with a regular x in the underlying cell.
I found some code on another forum that generates a list of values for each checkbox (vba - Obtain the value of an HTML Checkbox inserted in Excel worksheet - Stack Overflow).
Based on that, I recorded a macro to extract the html Name of a single checkbox and then set up a Vlookup for the True/False value. However, I can't figure out how to automate a vlookup for every individual checkbox and put the data in the appropriate underlying cell.
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 database of names in one column in excel 2010 and over time I have coloured the text of some of the names green to show that they have been added to a new online database. I want to be able to count how many of the names have been coloured so I can see if it matches with the total number already added on to the online database. I have over 800 entries so I don't want to have to manually count them unless I find I have missed any. Conditional Formatting is greyed out (it is a Shared document so that might be why).
in creating an IF (text is a certain colour) THEN 1, 0 or any others ways I can find the total in that colour. I only have two text colours in my spread sheet, black and light green.
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?
What I have done is entered code to auto generate the date in column O whenever data is entered or altered in column A. Here is that code:
VB: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A:A")) Is Nothing Then _ Target.Offset(0, 14).Value = Now
End If End Sub
What I am trying to do now, is create a column that will take the information from O and do a sort of COUNTIF function that will count how many items of data were entered on a certain date by the day. For a clearer example, I want it to tell me how many items were entered/altered on 7/23. But I also want it to continuously calculate it for each date after that. Preferably automatically, but if a macro is needed I can create an update button.
Once it can achieve that I would like to create a dynamic graph that will automatically (or via macro button) update to show the last 5 days. It should display the date and how many items were entered that day.
Based on the current month (which I planned on typing in) on the MainList worksheet I need to count how many blank cells are in the table above. The information above is on a separate worksheet but I can change that in the formula. Every month gets a new column.
This is what I have but its not working correctly: =SUMPRODUCT(--(BTES!AD1:BQ18=F2),--(BTES!AD1:BQ18" "))
How to sort out a way to count cells whose fill colors have been set via conditional formatting.
I'm sure that I was once able to use Chip Pearson's CountOfCF function back in the days of Excel 2003, as it worked around the 3 condition limit and used .ColorIndex which assumed that you'd only ever be formatting using the .ColorIndex values of 1 to 56, but I can't seem to be able to get it to work in 2010.
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 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.