COUNTIF, dates and blank cell criteria

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?

=SUM(IF((TODAY()>A1:INDEX(A:A,MAX(IF(ISBLANK(G1:G65535),0,ROW(G1:G65535))))+
28)*
(G1:INDEX(G:G,MAX(IF(ISBLANK(G1:G65535),0,ROW(G1:G65535))))=""),1))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"luvthavodka" <[email protected]> wrote in message
news:[email protected]...
> 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?

Thanks Bob, but i'm getting a #VALUE return with this. Have committed with
Ctrl-Shift-Enter...is there anything else I could have done wrong?

Regards

Jenny

"Bob Phillips" wrote:

> =SUM(IF((TODAY()>A1:INDEX(A:A,MAX(IF(ISBLANK(G1:G65535),0,ROW(G1:G65535))))+
> 28)*
> (G1:INDEX(G:G,MAX(IF(ISBLANK(G1:G65535),0,ROW(G1:G65535))))=""),1))
>
> which is an array formula, it should be committed with Ctrl-Shift-Enter, not
> just Enter.
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with googlemail if mailing direct)
>
> "luvthavodka" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
>
>
>

It might be the NG wrap-around. Try this version

=SUM(IF((TODAY()>A1:INDEX(A:A,MAX(IF(ISBLANK(G1:G65535),0,ROW(G1:G65535))))
+28)*(G1:INDEX(G:G,MAX(IF(ISBLANK(G1:G65535),0,ROW(G1:G65535))))=""),1))

still array entered

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"luvthavodka" <[email protected]> wrote in message
news:[email protected]...
> Thanks Bob, but i'm getting a #VALUE return with this. Have committed with
> Ctrl-Shift-Enter...is there anything else I could have done wrong?
>
> Regards
>
> Jenny
>
> "Bob Phillips" wrote:
>
> >
=SUM(IF((TODAY()>A1:INDEX(A:A,MAX(IF(ISBLANK(G1:G65535),0,ROW(G1:G65535))))+
> > 28)*
> > (G1:INDEX(G:G,MAX(IF(ISBLANK(G1:G65535),0,ROW(G1:G65535))))=""),1))
> >
> > which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
> > just Enter.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with googlemail if mailing direct)
> >
> > "luvthavodka" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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?
> >
> >
> >

Still suffering the #VALUE??!!! Thanks for your help anyway Bob....I shall
spend a few more hours trying to figure this one out...grrrrr...

"Bob Phillips" wrote:

> It might be the NG wrap-around. Try this version
>
> =SUM(IF((TODAY()>A1:INDEX(A:A,MAX(IF(ISBLANK(G1:G65535),0,ROW(G1:G65535))))
> +28)*(G1:INDEX(G:G,MAX(IF(ISBLANK(G1:G65535),0,ROW(G1:G65535))))=""),1))
>
> still array entered
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "luvthavodka" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks Bob, but i'm getting a #VALUE return with this. Have committed with
> > Ctrl-Shift-Enter...is there anything else I could have done wrong?
> >
> > Regards
> >
> > Jenny
> >
> > "Bob Phillips" wrote:
> >
> > >
> =SUM(IF((TODAY()>A1:INDEX(A:A,MAX(IF(ISBLANK(G1:G65535),0,ROW(G1:G65535))))+
> > > 28)*
> > > (G1:INDEX(G:G,MAX(IF(ISBLANK(G1:G65535),0,ROW(G1:G65535))))=""),1))
> > >
> > > which is an array formula, it should be committed with Ctrl-Shift-Enter,
> not
> > > just Enter.
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace somewhere in email address with googlemail if mailing direct)
> > >
> > > "luvthavodka" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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?
> > >
> > >
> > >
>
>
>

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 <[email protected]>,
luvthavodka <[email protected]> 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?

Thats option Domenic - option 2 worked a treat!

"Domenic" wrote:

> 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 <[email protected]>,
> luvthavodka <[email protected]> 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?
>