Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF, dates and blank cell criteria
=SUM(IF((TODAY()A1:INDEX(A:A,MAX(IF(ISBLANK(G1:G6 5535),0,ROW(G1:G65535))))+
28)* (G1:INDEX(G:G,MAX(IF(ISBLANK(G1:G65535),0,ROW(G1:G 65535))))=""),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" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF, dates and blank cell criteria
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:G6 5535),0,ROW(G1:G65535))))+ 28)* (G1:INDEX(G:G,MAX(IF(ISBLANK(G1:G65535),0,ROW(G1:G 65535))))=""),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" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF, dates and blank cell criteria
It might be the NG wrap-around. Try this version
=SUM(IF((TODAY()A1:INDEX(A:A,MAX(IF(ISBLANK(G1:G6 5535),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" wrote in message ... 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:G6 5535),0,ROW(G1:G65535))))+ 28)* (G1:INDEX(G:G,MAX(IF(ISBLANK(G1:G65535),0,ROW(G1:G 65535))))=""),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" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF, dates and blank cell criteria
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:G6 5535),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" wrote in message ... 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:G6 5535),0,ROW(G1:G65535))))+ 28)* (G1:INDEX(G:G,MAX(IF(ISBLANK(G1:G65535),0,ROW(G1:G 65535))))=""),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" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF, dates and blank cell criteria
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.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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif non blank cells + dates overdue | Excel Worksheet Functions | |||
=IF cell needs to show blank if blank | Excel Discussion (Misc queries) | |||
what means 'blank cell'?? | Excel Worksheet Functions | |||
Search Range for Criteria in given cell and produce results | Excel Discussion (Misc queries) | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel |