Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

COUNTIF function with Dates & due dates.

I am trying to create a database with due dates for work. For Example.

Due in 7 Days =COUNTIF(A7:A40,"<="&TODAY()+7)-COUNTIF(A7:A40,"<"&TODAY())
Due in 30 Days =COUNTIF(A7:A40,"<="&TODAY()+30)-COUNTIF(A7:A40,"<"&TODAY())
Due in 60 Days =COUNTIF(A7:A40,"<="&TODAY()+60)-COUNTIF(A7:A40,"<"&TODAY())
Due in 90 Days =COUNTIF(A7:A40,"<="&TODAY()+90)-COUNTIF(A7:A40,"<"&TODAY())
Lapsed<Today =COUNTIF(A7:A40,"<"&TODAY())

I think these work for me in excel, but I'm not a master at this and for some reason when I read my functions I swear they are contradicting themselves. The reason I feel the functions contradict themselves is because I can remove the -countif and it still gets the same answer.

For the date ranges 90, 60, 30 & 7 I do not want dates counted that are less than "today" or ex. = a negative number. They would be considered "Lapsed"

Please see the attached file. Any help would be greatly appreciated!!!!!

Kevin


Post your answer or comment

comments powered by Disqus
hi guys,

was wondering how to use countif function to count for overall months.

I have a column with numerous dates in dd/mm/yyyy format. I would like to count number of cells with a certain year, regardless of day and month. How do you do this?

At the moment, the countif function only works if I enter a specific date. E.g COUNTIF(A1:A3000, "1/31/1994"). I have tried substituting x, *, ~ for the month and day, but the count function still just returns 0.

Jodapo.

I am trying to combine an if function with dates:

my formula reads: =IF(C2

I am trying to combine an if function with dates:

my formula reads: =IF(C2<"10/01/04",B2,"")

It is not working...can anyone help?

Hi

I need to know a way of doing a COUNTIF function with two conditions inside it, my current formula is
, and I want to add another check to range 'Master data'!P$6:P$156 = "someText".  I only want it to be counted if
both conditions are true.

I hope this makes sense,

Thanks in advance

Dale.

Dear Gurus,

I have this small formula I am struggling about.

I need to do a countifs function with a specific requirement. Count should be done only from the date specified (lets say current date). Dates and the Count range are Parallel Rows. I have attached a spreadsheet with a sample scenario here Book1.xlsx

Thanks in advance

Hi,

I have what I'm sure is a simple question. Can I use a Countif function on a column of dates, asking it to only include dates from the last 7 days?

eg. =COUNTIF('Dates Sheet'!A:A,"THE LAST 7 DAYS")

where A:A is full of dates in DD/MM/YYYY format.

Hope this makes sense! Help appreciated.

Good Morning All,

I'm trying to count cells if a calculated value of two dates meets my criteria.
For example I have two columns of dates (Date2 and Date1). I want to count if
Date2 - Date1 = less than 1 year, between 1 and 5 years, between 5 and 10 years, ...
I can create a third column for Date2-Date1 and use countif to count it but I don't want to do that.

Date2 Date1
1/01/2012 1/01/2011
1/02/2011 30/05/2008
5/04/2012 1/09/2005

Any help will be appreciated.

Cheers,

Jayana

This is a problem I've been trying to work out...and I am currently at my wit's end.

This may be a little murky but I'll try to make it as clear as possible (feel free to ask for clarification on any of it if you think you could help).

I've got two tabs of and Excel sheet "Data" and "Summary".

The Data tab contains 4 columns:
A - a shipment date (mm/dd/yy)
B - a return date (or current date if not returned yet - also mm/dd/yy)
C - a calculation of months in the "field" as a function of MONTH B-A
D - if the shipment was returned, the # months in the field from column C, otherwise blank
Each row is a separate shipment, and there's lots of them!

The Summary tab also contains 4 columns:
A - the first date of every month (mm/dd/yy)
B - a calculation (count) of shipments for the month starting with date listed in A from all the shipments in Data-A
C (this is what I need help with) - need a formula to calculate the count of all occurences in Data-D that fall between 0 and 2 (returns within 2 months of shipment) for that month (as specified in Summary-A)
D (also need help witht his one) - similar to C above, but a count of returns within 3-12 months for that same month

Is there a worksheet function that can accomplish this?
I'd like to avoid using macro's or pivot tables.

Thanks.

Here is the failing function with the explanation below:

=IF(AG4>0, "Failed", IF(COUNTIF(Compliance,"P")=0,"N/A",IF(COUNTIF(Compliance, "L")>0, "Partial Pass", "Passed")))

I have a spreadsheet where I record the pass/fail status of criteria for projects. These criteria are all contained in sequential columns. The result in the final column is whether or not the project passed, failed, passed in part, or is not applicable to a pass/fail status. The overall pass/fail status works just fine.

Some of these criteria are managed by different people, and I'd like to pull data from the original columns as to how they are performing specifically. I could just rearrange the criteria to be sequential so I could use a COUNTIF with a range, but I ALSO want to pass/fail other criteria based on each stage of the project.

So in a nutshell: I want to perform a COUNTIF on unique cells that do not occur in a linear, sequential range. What I've tried to do so far was name these cells "Compliance" and then use the name as the range within the COUNTIF function, but it returns a "#VALUE" error.

Any advice?

Hi,

I have a problem using COUNTIF function. I want to count cells say in range A1:A10 that are smaller that a value in a cell let's say B1. Neither of these works:

=COUNTIF(A1:A10; "

I'm trying to create a countif function with three criteria, but for the life
of me I can't figure out the nested function. For example, I want to countif
column A=1, and column b=2 and column c=3. Can anyone help me with this?

Thank you so much!

Hi,
I am trouble getting the right results using the IF function with dates. I would like the If function to compare 2 dates (some in different years) and if one date 5/30/2011 is before the second date 3/31/2012 then yes value should be zero and if it is not then I would like it to calculate an amount. But the outcome is always showing the calculation and never a zero regardless of dates.
Can you please help,
Thank You,
I am attaching a sample of what I am trying to do.
=IF(D6<J3,0,F6*J4/36*3)

Is it possible to have multiple criteria for a countif function?
Thanks.

I need to put together a countif function that counts all the occurances of a particular date, this date is displayed in a given cell, for the purposes of this example I will say D3. D3 contains its own formula to determine which date is displayed. I currently have:

=COUNTIF(Active!$B$3:$B$65536,D3)

but this results in 0 everytime. I have a hunch that its due to the formula within cell D3, I know this because when I replace the formula with the resulting date it changes to the number I'm looking for.

Any advice?

I'm in QA and am trying to create a table for tracking build verification reports based on 3 criteria:

-Date (only accept entries within the past week)
-Conditional (for this example, I will count how many verification passed)
-Branch (the branch the build is based off of, for this example, branches will be “Primary” and “Tertiary”)

Through some help here, I found that the =countifs function works best, and I was able to count how many builds passed based upon the following formula:
=COUNTIFS('BVT History'!B2:B1000, ">="&TODAY()-6,'BVT History'!C2:C1000,"Pass")
Where the ‘B’ range is dates, and the ‘C’ range is conditionals.

I’m almost done with this table; however, needed to enter functionality where the user types in the name of the branch they want results for in cell “C2” and the range is A2:A100 on the BVT History workbook. So I figured the following formula would work:
=COUNTIFS('BVT History'!B2:B1000, ">="&TODAY()-6,'BVT History'!C2:C1000,"Pass", 'BVT History'!A2:A1000, "=C2")

However, it doesn’t. Do you know what I’m doing wrong here? I tried hard coding in conditionals as well, but it seems like the countifs function breaks when applying a third criteria field.

Hey,

I need help with a COUNTIFS function (I think that will be the best function to use at least).

I have a two columns of data, column one is dates (mm/dd/yyyy) and column two is states (AK, MA, MI, etc). I need a way to count how many times a states comes up for a certain month. For example, I need to know how many times MA appears through the dates 1/1/2007 and 1/31/2007.

Here's what I have so far. It works but I can only specify one date. Any help you can provide would be greatly appreciated.

=COUNTIFS('SHEET NAME'!D:D, "1/5/2007", 'SHEET NAME'!E:E, "MA")

Hi - i'm new to this forum so please forgive me if this has been covered elsewhere, but i couldn't find it...

I need to count rows that meet 2 criteria.
I have seen this help page
http://www.ozgrid.com/Excel/count-if.htm
but that counts rows with "criteria 1" OR "criteria 2"...
I need to count rows that fulfill "criteria 1" AND "criteria 2"
ie - count the rows that have todays date AND a cell that says "COMPLETE"

ideally it would be as easy as "=countif(A:F,"today()","COMPLETE") but that doesn't work... any way around this???

Thanks!

I am having problems using the countif function referencing to a cell using the If function. What i am trying to achieve is if cell C5 or any other of the orange highlighted cells has data in it then a corresponding date will be placed into cell L5 through to L13. cell M27 counts the number of cells that have a date in them. however this countif is not working. Could somebody please help as I cant seem to figure out a way of making it work without using more cells and hiding the contents.

I have attached the sheet if it makes things easier to understand. the problem cells are highlighted in purple.

Many thanks.

Hi

Working with Excel 2007 to do various word related functions, and discovered that, even when a cell is formatted as text, the word 'false' is not counted by the COUNTIF function.

I have a range that includes the words false and true, and while a logical function recognises the individual cells to be the same as a separate cell with the word (also as text), a countif function to search for instances of the word within the range returns 0.

Can anyone explain why this is, and if there is any easy way around it?

Much appreciated,

Ant

I have been using a spreadsheet to track the number of leads that have come in over the past few months. The first column holds the dates that the lead came in. We'll call the first worksheet "Jack's Sheet" then on the next worksheet (we'll call that one "Jack's Report")in the workbook I am creating a report to tally the number of leads per month. I have the dates transponded in the Jack's Report in the Excel 5 digit date form i.e. 38122 so all I need is to create a countif function that counts the number of times a date within a certain month appears in the column. Or if you have an easier way I would love to hear it.


A

B

C

D

E

F

G

1

Q1

Q2

Q3

Q4

Joe

2

01/01/2008

04/01/2008

07/01/2008

09/01/2008

Tom

3

03/31/2008

06/30/2008

08/31/2008

12/31/2008

Jess

4

Date

Name

5

01/05/2008

Joe

Joe

6

01/06/2008

Tom

7

01/07/2008

Jess

8

01/08/2008

Joe

9

02/05/2008

Tom

10

02/06/2008

Jess

11

TOTAL

1

I am finding totals by a specific date range (Quarters, Q1, Q2...) and by a name. IE: How many times did Joe show up within Q1. I have the following formula where you see C5(Joe):

=IF(AND(C2=A5,G1=B5),B5,"")

And in C11 I have a totals forumla:

=COUNTIF(C5:C10,G1)

It is working, however with the amount of data I am calculating it causes considerable slow down.

I've been experimenting with the countif function.
IE:=COUNTIF(A5:A10,AND(C2=A5,C5=G1))
Which has not been working.

Any thoughts or suggestions on how to streamline this / clean it up would be appreciated.

I am using Excel 2002, I have a spreadsheet with ~200 rows and ~75 rows. Column A is sorted to be in numerical order, column b is date field mm/dd/yyyy, column C is a pull down with Y/N/NR/NA values. I need to figure out how to do a count if statement that says any date in column B with value "Y" from column C. Since the dates in Column B aren't in chronological order, sorting it first and then doing a general countif statement wont work in this situation, becuase I already have a countif statement setup based on the fact that column A is sorted in numerical order.

A B C D
1 1/1/2007 Y
2 12/15/2006 N
3 4/5/2006 Y
4 3/3/2007 NR
5 11/20/2007 Y

Desired results - COUNTIF Date range 1/1/2007-12/31/2007 , values in Column C

Results:
2 - Y
1 - NR

Thanks for helping

I need help with the following functions for dates. In Column A I have a
start date (including time) and in Column B I have the end date (including
time). I have been tasked with finding out if the end time is past 8:00 a.m.
each day. If the end time is past 8:00 a.m. I must place a “Y” in Column C
or an “N” in Column C.
This out my data looks:

Column A Column B
Start Time End Time
10/20/2005 23:00 10/20/2005 23:53
10/20/2005 10:00 10/21/2005 8:30
How do I check the time with the date being in the beginning of this data?
The date maybe the same day or could be the next day.

Thanks!

Hi,

I have 2 columns of data in sheet 1 as follows:
B C
Open 08/30/05
Closed 08/01/05
Closed 08/30/05
Open 08/03/05
Closed 07/01/05
Closed 07/02/05

In sheet 2, I would like to count how many cells a
1. have august as their dates
2. open on the month of august
3. closed on the month of august
4. etc.

so far, i'm trying this count if formula out, but it doesn't work.
1. =COUNTIF(Sheet1!$C$1:$C$65000,"08/**/**")
2.
=SUM(IF((Sheet1!$B$1:$B$65000="Open"),IF(Sheet1!$C $1:$C$65000="08/**/**",1,0)))
3.
=SUM(IF((Sheet1!$B$1:$B$65000="Closed"),IF(Sheet1! $C$1:$C$65000="08/**/**",1,0)))

i don't know if it's the wildcard characters, or if it's the format of
my date. please help!! thanks!!

--
chinita_jill
------------------------------------------------------------------------
chinita_jill's Profile: http://www.excelforum.com/member.php...o&userid=36536
View this thread: http://www.excelforum.com/showthread...hreadid=562912


No luck finding an answer? You could always try Google.