I have a column with dates that I'd like to use a count formula where it
counts the total number of rows that are past a certain date. How
can I write the formula to calculate this count? Below is an example of what
I mean. A1 is the cell for the date criteria that will determine how many
dates are past due in column B. Column B will have mixture of date and text.
The count should include text as overdue when calculating the number past due.
The answer based on the example below should be 7. Can anyone help me create
this formula?
A1 = 11/25/08

Column B
12/31/08
10/31/08
3/1/13
12/31/08
11/30/08
12/31/08
1/15/09
12/30/08
9/9/09
12/30/08
12/31/08
Not Started
12/30/08
1/30/09
12/31/08
Rqstor on Lv
11/30/08
1/31/09
12/31/08
12/31/08
12/30/08
On Hold
12/30/08
On Hold
12/31/08
12/31/08
10/10/08
1/31/09
12/30/08
11/30/08
1/30/09
4/15/09
TBD
1/30/09
12/31/08
6/30/09
1/31/09
12/15/08
1/31/09

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

You need to use SUMPRODUCT here like this:

= SUMPRODUCT((B:BA1)*(B:B<"N/A"))


--
The Code Cage Team

Regards,
The Code Cage Team
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=37582

This formula counts more than the 7 it should. Am I doing something wrong
because it should only count 7, but it's counting more than that. Also need
to modify the formula to only count text that is "TBD".

The Code Cage Team wrote:
You need to use SUMPRODUCT here like this:

= SUMPRODUCT((B:BA1)*(B:B<"N/A"))


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

The answer based on the example below should be 7.

Shouldn't the answer be 37?

I get 37 using this formula:

=SUMPRODUCT(--(B1:B39A1))

Note that with SUMPRODUCT you *can't* use entire columns as range references
unless you're using Excel 2007.

--
Biff
Microsoft Excel MVP


"AHizon via OfficeKB.com" <u38169@uwe wrote in message
news:8e3726a3bbdae@uwe...
I have a column with dates that I'd like to use a count formula where it
counts the total number of rows that are past a certain date. How
can I write the formula to calculate this count? Below is an example of
what
I mean. A1 is the cell for the date criteria that will determine how many
dates are past due in column B. Column B will have mixture of date and
text.
The count should include text as overdue when calculating the number past
due.
The answer based on the example below should be 7. Can anyone help me
create
this formula?
A1 = 11/25/08

Column B
12/31/08
10/31/08
3/1/13
12/31/08
11/30/08
12/31/08
1/15/09
12/30/08
9/9/09
12/30/08
12/31/08
Not Started
12/30/08
1/30/09
12/31/08
Rqstor on Lv
11/30/08
1/31/09
12/31/08
12/31/08
12/30/08
On Hold
12/30/08
On Hold
12/31/08
12/31/08
10/10/08
1/31/09
12/30/08
11/30/08
1/30/09
4/15/09
TBD
1/30/09
12/31/08
6/30/09
1/31/09
12/15/08
1/31/09

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

I'm using Excel 2007 and the answer should only be 3 when it counts the dates
before 11/25/08 and "TBD" cells. I want to show only dates that are overdue
and "TBD". How can I get the formula to only count the dates before a date
criteria and "TBD"?

T. Valko wrote:
The answer based on the example below should be 7.

Shouldn't the answer be 37?

I get 37 using this formula:

=SUMPRODUCT(--(B1:B39A1))

Note that with SUMPRODUCT you *can't* use entire columns as range references
unless you're using Excel 2007.

I have a column with dates that I'd like to use a count formula where it
counts the total number of rows that are past a certain date. How
[quoted text clipped - 50 lines]
12/15/08
1/31/09

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

counts the dates before 11/25/08 and "TBD" cells

Try this:

=COUNTIF(B1:B50,"<"&A1)+COUNTIF(B1:B50,"TBD")

--
Biff
Microsoft Excel MVP


"AHizon via OfficeKB.com" <u38169@uwe wrote in message
news:8e5d4cc835056@uwe...
I'm using Excel 2007 and the answer should only be 3 when it counts the
dates
before 11/25/08 and "TBD" cells. I want to show only dates that are
overdue
and "TBD". How can I get the formula to only count the dates before a
date
criteria and "TBD"?

T. Valko wrote:
The answer based on the example below should be 7.

Shouldn't the answer be 37?

I get 37 using this formula:

=SUMPRODUCT(--(B1:B39A1))

Note that with SUMPRODUCT you *can't* use entire columns as range
references
unless you're using Excel 2007.

I have a column with dates that I'd like to use a count formula where it
counts the total number of rows that are past a certain date. How
[quoted text clipped - 50 lines]
12/15/08
1/31/09

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1