COUNTIF, dates and blank cell criteria
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.99 999999999999E+307,Sheet
1!$A$2:$A$65536))
Click Add
Name: RangeY
Refers to:
=Sheet1!$G$2:INDEX(Sheet1!$G$2:$G$65536,MATCH(9.99 999999999999E+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 ,
luvthavodka 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?
|