Re: COUNTIF, dates and blank cell criteria

Giganews Newsgroups
Subject: Re: COUNTIF, dates and blank cell criteria
Posted by:  Domenic (domenic…@sympatico.ca)
Date: Thu, 01 Jun 2006

Maybe, a couple of possibilities...

1) If you have Excel 2003 or later, convert the data into a list...

Data > List > Create List

Then, assuming that A2:A100 contains the date, and G2:G100 contains the
corresponding data, try...

=SUMPRODUCT(--(A2:A100<>""),--(A2:A100<TODAY()-28),--(G2:G100=""))

The range will automatically adjust as you add/remove data.

2) Define the following named ranges...

Insert > Name > Define

Name:  RangeX

Refers to:

=Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$65536,MATCH(9.99999999999999E+307,Sheet
1!$A$2:$A$65536))

Click Add

Name:  RangeY

Refers to:

=Sheet1!$G$2:INDEX(Sheet1!$G$2:$G$65536,MATCH(9.99999999999999E+307,Sheet
1!$A$2:$A$65536))

Click Ok

Change the sheet reference accordingly.  Then, try the following
formula...

=SUMPRODUCT(--(RangeX<>""),--(RangeX<TODAY()-28),--(RangeY=""))

Hope this helps!

In article <B8D96D87-169F-4DAD-ACFB-B13C6FC2AC…@microsoft.com>,
luvthavodka <luvthavod…@discussions.microsoft.com> wrote:

> I'm looking to count the number of blank cells in column G (only upto the
> bottom of the data table I'm using - the length of which is unknown until the
> end of the month, but only if the corresponding cell in column A (a date) is
> greater than 28 days old. What formula should I use?

Replies

In response to

COUNTIF, dates and blank cell criteria posted by luvthavodka on Mon, 29 May 2006