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

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

- Using Countif function with dates
- If function with dates
- If function with dates
- COUNTIF function with two conditions
- CountIFS based on Date
- Can I 'Countif' cells with dates from 'the last 7 days' etc
- COUNT/COUNTIF function with date calculation
- Multi-criteria countif functions for dates...possible?
- How do you perform the COUNTIF function with a range that is not linear?
- Countif Function With An Inequality Reference To A Cell
- Countif Function -Nested
- IF Function using dates
- Countif function with multiple criteria
- COUNTIF with Date formula
- Help with the =COUNTIFS function
- COUNTIFS With Date Range
- COUNTIF function with multiple criteria???
- Countif function dosent work with other formulae
- FALSE or TRUE as text - odd behaviour with COUNTIF function
- COUNTIF function for counting dates within a month
- Countif by a date range & name
- COUNTIF Stmt with date range
- Help with Date Functions
- Need help on countif and sumif function with dates and wildcard characters

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.

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

It is not working...can anyone help?

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.

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

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.

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 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.

=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?

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; "

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!

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)

=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?

-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.

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")

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 have attached the sheet if it makes things easier to understand. the problem cells are highlighted in purple.

Many thanks.

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

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.

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

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!

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.