Free Microsoft Excel 2013 Quick Reference

Counting instances of "N" within a date range

Hello all,

I am working on a spreadsheet with what will be a year's worth of data (as of Oct. 2007). I am setting up another spreadsheet to which I want to pull quarterly data, but right now I am having trouble counting instances of "N" within a set date range. I have been able to use =sumproduct((range>=[cell referencing date Q1 starts])*(range<=[cell referencing date Q1 ends])) to define Quarter 1. I will then repeat for Q2-4.

My next step is to count instances of "N" in Q1 only. I tried to nest my sumproduct in an if(and(... but that wants to return a true or false value and I need the total number of "N"s. I am trying to avoid adding a column and totaling the "true value"s at the bottom. Below are the three columns of "dummy data" with which I am working:

Name | Enroll Date | Exercise
Smith, John 9/24/2006 N
Smith, Jane 10/1/2006 N
Doe, John 10/13/2006 Y=>3x/wk
Doe, Jane 11/1/2006 N
Green, John 11/20/2006 N
Green, Jane 11/30/2006 Y=>3x/wk
Brown, John 12/1/2006 N
Brown, Jane 12/7/2006 N
Black, John 12/21/2006 Y=>3x/wk
Black, Jane 12/31/2006 N
Knight, John 1/1/2007 N
Knight, Jane 1/17/2007 Y=>3x/wk

Q1 Start date: 10/1/06
Q1 End date: 12/31/06

I've been stuck for days! Any help would be much appreciated!

Audra


Hi,

I want to count the number of cells within a specific range. For example, if have the data below in cells within one column:

20.04
20.04
19.60
19.45
19.41
18.95
18.92

and at the bottom of the column I want to count up how many are betweeen >18 and <20, and how many are >19 and <21 etc. I dont want to add up the >18s from the entire range and then subtract the <20s from the entire ranges, I want to know how many >18 are ALSO <20 etc. I hope that make sense and someone can point me in the right direction.

Thanks Greg.

I am trying to count the number of occurences of a text item within a date
range. I have tried using the COUNTIF function, but since my date is in
DD/MM/YYYY format I am unable to single out the year (ex "2004"). Example,
total number of Safety Classes in 2004 - with one column as the date and
another column listing the safety class. I can count the total number of
occurences of the safety class, I'm just having issues coordinating it with a
date range.

I am trying to count unique numbers within a date range. Where column A containes a list of dates and column B contains a list of numbers.

I used
=SUMPRODUCT((B5:B317<>"")/(COUNTIF(B5:B317,B5:B317)+(B5:B317="")))
to count the unique numbers throughout the entire range. But what I would also like to do is narow it down by month.

I also used
=SUMPRODUCT(--(D5:D317="Approved"),--(A5:A317>DATE(2004/12/31)),--(A5:A317<DATE(2005/2/0)))
To count the number of "approved" numbers for a month.

I would like to use part of each formula to create a new formula that would give me the unique numbers within a date range.

Any help would be greatly appreciated.

I am trying to count the number of occurences of a text item within a date
range. I have tried using the COUNTIF function, but since my date is in
DD/MM/YYYY format I am unable to single out the year (ex "2004"). Example,
total number of Safety Classes in 2004 - with one column as the date and
another column listing the safety class. I can count the total number of
occurences of the safety class, I'm just having issues coordinating it with a
date range.

I would like to count if a date falls within a date range.

Sample:
Name Arrival Date Departure Date
Bob 1 Jan 2007 8 Jan 2007
Joe 2 Jan 2007 10 Jan 2007
Peter 1 Jan 2007 8 Jan 2007

Question:
How to calculate how many people are present on 1 Jan
How to calculate how many people are present on 2 Jan etc.

Is this possible?

I have an excel list of each book order including # of books ordered, # of pages in book and the date it was prepared for printing.
I need to create a new report showing total orders with total # of books and total # number of pages for all books printed within a date range. The date range should be for those printed by the Saturday at the end of each week.

Need help with creating formulas for this info. Any help is appreciated!!
I've attached the sheet for reference - Sheet 2 is where I am looking to build the report

I have a large file with column g with names, column a with serial numbers
and column d with dates for several thousand rows. They are randomly dated
according to as to when the serial number is first accessed. I need to find
all the rows within a date range such as 04/01/2004 and 04/05/2004.
Can this also place these entire rows into a new sheet (same file).

Hi,

I want to determine number of saturdays in a date range, i.e if we are provided with a start date and a end date then how can we calculate the number of staurdays b/w those two days?

Start date & End date can be a week day or a weekend.

Please help.

Regards,
Casper

Good morning,

I'm after a bit of help on a spreadsheet that i am doing. I want to count the number of occurences of the letters "YR" on the sheet named tracker within the date range of 01/07/08 to 31/07/08.

Currently ive got up to where i can count the number of occurences of the letters YR, but i cannot get the formula to count the number of occurences of those letters within a specific date. My formula looks like this: =COUNTIF(Tracker!C2:C335,"YR")

Thanks for any help in advance

I'm trying to count the number of occurances by department that fall within a specific date range(in this case monthly....ie 08/01/07 - 08/31/07)

The formula I'm using is as follows:

=COUNTIF('Filled Reqs'!$R$2:$R$389,">08/01/07")- COUNTIF('Filled Reqs'!$R$2:$R$389,"

Hi,
Can someone help me - I need to count the amount of instances of a text
value within a date range. I have date columns and text columns in a table
(the data is extracted from MS-Project).

eg. I am trying to count how many times "poured slab" happened in the past
week.

I have tried several things, no luck!

Is there a way to count only the cells that fall within a defined date range?
Example:
Count only the number of cells in a range that fall within the date range of
7/01/06 through 7/31/o6.
Thanks for any help
--
Dewayne

Hi,
Can someone help me - I need to count the amount of instances of a text
value within a date range. I have date columns and text columns in a table
(the data is extracted from MS-Project).

eg. I am trying to count how many times "poured slab" happened in the past
week.

I have tried several things, no luck!

Is there a way to count only the cells that fall within a defined date range?
Example:
Count only the number of cells in a range that fall within the date range of
7/01/06 through 7/31/o6.
Thanks for any help
--
Dewayne

Hi, I'm new to the forum so please do forgive me if this is a very obvious question - i'm just begginning to use the extended function of formula in Excel.

Is it possible to create a formula that Counts if a cell is not empty (or containing a letter "Y") within a date range defined by a specific date. I.e I would like to find out how many columns contain a letter Y on 13/09/07.

Thanks for you help.

Pete

Hi,

I have two columns with dates in my dataset, which define
the start and end dates for a certain event. For each
observation in the dataset, I'd like to count the
number of other observations in the dataset that
overlap with this event window.

I.e.

Event Start date End date Simultaneous events
-----------------------------------------------------------------------------------
XYZ 11jan04 07feb04 ???
ZZZ 11jan04 08feb04 ???
YYY 14jan04 22jan04 ???
XXX 21jan04 13mar04 ???
ZZY 30jan04 04feb04 ???

aso.aso.

Many thanks for your help!

Hello everyone,

This is my first post and being a novice on Excel i would appreciate any help i can get. I have Excel 2002(I know it's old but it works - just like me!!) and have a particular problem as follows:-

I have tried various sumif and sumproduct and even combination of both, but cannot get this to work.

I have the equation below to produce any costs that fall between January 1st 2007 and 31st January 2007 (with the cost column being N) which works fine:-

=SUMPRODUCT(--(S16:S2000<=DATE(2007,3,31)),--(S16:S2000>=DATE(2007,3,1)),N16:N2000)

I also have this one which looks for anything that begins with an 'F' in the H column and works out the value (N column again). This also works fine.

=SUMIF(H16:H2000,"*F*",N16:N2000)

What I want to do though is produce values that begin with an 'F' but fall within the date range 1st Jan 2007 - 31st Jan 2007.

It's probably dead easy but my brain hurts now.

Any help would be very much appreciated.

Thanks

I have a worksheet in which I am trying to count the number of records in a
date range. For example, the Submit_Date column contains the following
dates/times (cells a1:a5):

12/17/2005 12:00:54 PM
12/16/2005 1:00:54 PM
12/17/2005 9:00:13 PM
12/10/2005 7:54:16 AM
12/17/2005 10:04:11 AM

I would like to count the number of records with a Submit_Date between
12/1/2005 12:00:00 PM and 12/31/2005 11:59:59 PM.

Any suggestions?
--
Jim
--
Jim

Hello, I have a question about sorting information using dates but using a range. I need this to sort when I enter a date range into the spreadsheet (the blue blocks on the attached spreadsheet). The factors in colum G need to be sorted by month and input into the yellow highlighted area in column B. There may be an easier way of pulling the data from the "Data" sheet but this was the best I could figure out. Overall, January (or any of the months)needs to have the January (or appropriate) factor that fits within the date range that was input but sorted properly based on the months listed in Column A.

At this point I'm just beating my head against a wall and any help would be appreciated.

Thanks,
Nick

edit: Solved, thanks!

I have a column in a spreadsheet that consists of dates in dd/mm/yyyy format.
I'm trying to write a formula that looks at the dates by row in the colum
and if the date falls within a certain range (eg from 01/04/2004 to
01/06/2004) then I want to sum the values in another column. I've worked out
that I need to use the sumif function but I can't work out how to analyse the
date range.

How do I tell the function to look at the dates in the column and if they
are within a certain range to sum the values in another column. As I said,
I'm pretty sure I need to use the sumif function - I just can't work out how
to specify that only dates within a certain period should be chosen.

Can any body help me work this out?

Please accept my apologies for posting this question in two groups. I only
found the excel worksheet functions group after I had posted in the excel
general questions group.

Thanks in advance.

Marcus

Hi, sorry if this has previously been posted and solved but the search funtion doesnt work on my browser at work.

I have a large spreadsheet which holds lots of data with date ranges that i need to performs different actions to. im currently trying to figure out a way to identify the number of days, per calender month, that falls in a date range.

sample data...

Start Date End Date Old Value New Value 08/03/2010 18/06/2010 16758.2 16758.1 19/06/2010 04/08/2010 16758.2 -224147.3 05/08/2010 17/01/2011 16758.2 2923.1

i need to break down the total number of days per month

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 24 30 31 18 12 31 4 27 30 31 30 31

As you can see this also laps into a new year, which poses my next problem, ill probably just add more columns on to the end of the table for that though...

I will later apply different calculations to these cells but in short need to get a calculation for the number of days per month first.

(in short spreading the new value out accross the year then multiplying it by the days... i also need to apply a further daily volume cal to it)
can any one help at all please?

Ta
Ben

Hi All,

I have this formula to count number of incidents within a specified month:

=COUNTIFS(A:A,">=1/1/2012",A:A,"<=1/31/2012")

-----------------------------------------------------

Now, on the following column, I'm trying to find a formula that counts the number of incidents resolved (YES) within that same month.

I've tried using the same formula with the additional argument (I:I,"YES") but it did not work.

Can somebody please help me out?

COUNTIFS, SUMPRODUCTS, DCOUNT..........I could not get any of those to work

I've been trying to figure it out the past few hours, and it's driving me insane

Any help is greatly appreciated. Thank you in advance!

Hello, I have a spreadsheet that has a Preferred Day of the Week column and a date range column from which to select a date to conduct inventories. Is there a formula that will automatically select the best date for an inventory based on the required date range AND a specific day of the week. For example, if one store must be inventoried on a Thursday between 5/5 and 5/13, is there a formula that will select 5/10 so that I don't have to manually type that date in my spreadsheet?

just found this site, and was hoping someone could help.

i am creating a customer tracking spreadsheet and want to be able to sum hits by a date range. for example, i've got individual dates in column A, and numbers that i want to sum in column B (and column C, D, etc.). i want to sum the data in column B by a date range, i.e., 8/1/04 - 8/7/04 (column A).

i'm having problems setting the range to work properly. i've tried "if" and "sumif" functions and come close, but the problem i'm having is setting the lower cutoff. for example i've tried:

IF(a6:a2000