Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use multiple criteria with COUNTIF: between dates and not blank
Using Excel 2003 I need to count rows where the date in column A is in a
particular month and year and column G is not blank. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use multiple criteria with COUNTIF: between dates and not blank
=SUMPRODUCT((YEAR(A1:A999)=2006)*(MONTH(A1:A999)=6 )*(G1:G999<""))
HTH -- AP "l.shields" a écrit dans le message de news: ... Using Excel 2003 I need to count rows where the date in column A is in a particular month and year and column G is not blank. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use multiple criteria with COUNTIF: between dates and not blank
=SUMPRODUCT(--(G2:G20<""),--(MONTH(A2:A20)=1))
for January Note that SUMPRODUCT doesn't work with complete columns, you have to specify a range. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "l.shields" wrote in message ... Using Excel 2003 I need to count rows where the date in column A is in a particular month and year and column G is not blank. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use multiple criteria with COUNTIF: between dates and not blank
oops, mis-read it. I see that now seeing Ardus' response.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Bob Phillips" wrote in message ... =SUMPRODUCT(--(G2:G20<""),--(MONTH(A2:A20)=1)) for January Note that SUMPRODUCT doesn't work with complete columns, you have to specify a range. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "l.shields" wrote in message ... Using Excel 2003 I need to count rows where the date in column A is in a particular month and year and column G is not blank. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use multiple criteria with COUNTIF: between dates and not blank
You're the fastest shooter West of Colorado, Bob!
Cheers, -- AP "Bob Phillips" a écrit dans le message de news: ... oops, mis-read it. I see that now seeing Ardus' response. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Bob Phillips" wrote in message ... =SUMPRODUCT(--(G2:G20<""),--(MONTH(A2:A20)=1)) for January Note that SUMPRODUCT doesn't work with complete columns, you have to specify a range. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "l.shields" wrote in message ... Using Excel 2003 I need to count rows where the date in column A is in a particular month and year and column G is not blank. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use multiple criteria with COUNTIF: between dates and not blank
I'm actually East of Colorado!
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Ardus Petus" wrote in message ... You're the fastest shooter West of Colorado, Bob! Cheers, -- AP "Bob Phillips" a écrit dans le message de news: ... oops, mis-read it. I see that now seeing Ardus' response. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Bob Phillips" wrote in message ... =SUMPRODUCT(--(G2:G20<""),--(MONTH(A2:A20)=1)) for January Note that SUMPRODUCT doesn't work with complete columns, you have to specify a range. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "l.shields" wrote in message ... Using Excel 2003 I need to count rows where the date in column A is in a particular month and year and column G is not blank. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use multiple criteria with COUNTIF: between dates and not bla
Many thanks guys for the very quick response. Worked perfectly. Sorry it
took me so long to reply, but I wasn't able to verify my e-mail address. "Ardus Petus" wrote: =SUMPRODUCT((YEAR(A1:A999)=2006)*(MONTH(A1:A999)=6 )*(G1:G999<"")) HTH -- AP "l.shields" a écrit dans le message de news: ... Using Excel 2003 I need to count rows where the date in column A is in a particular month and year and column G is not blank. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use multiple criteria with COUNTIF: between dates and not bla
Hello,
I have a similiar problem but I need to count the number of counts in EVERY bin of a graph seperately. i.e. How many have an (x,y) of (0,0) (0,1)(1,0) and so on. For the numbers I am talking about I need to do this several thousand times and copying the formula below doesn't work because it changes the column it's looking at every time copy to a new cell. Is there another way that can be easily copied? Thanks, Eliza "Ardus Petus" wrote: =SUMPRODUCT((YEAR(A1:A999)=2006)*(MONTH(A1:A999)=6 )*(G1:G999<"")) HTH -- AP "l.shields" a écrit dans le message de news: ... Using Excel 2003 I need to count rows where the date in column A is in a particular month and year and column G is not blank. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use multiple criteria with COUNTIF: between dates and not bla
I have a similar issue: I have a range of dates that I'm trackun using the
COUNTIF function based on the dates that are 91-180, 181-270, & 271-365, days old, based on the TODAY date. for the 90-180days, I'm using the following, but doesn't pan out: =COUNTIF(MASTER!AI5:AI5997,"="&TODAY()-180), (MASTER!AI5:AI5997,"<="&TODAY()-90) Any help would be graetly appreciated!! Keith |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use multiple criteria with COUNTIF: between dates and not bla
Try this:
=SUMPRODUCT(--(MASTER!AI5:AI5997=TODAY()-180),--(MASTER!AI5:AI5997<=TODAY()-91)) -- Biff Microsoft Excel MVP "Nascarfan88" wrote in message ... I have a similar issue: I have a range of dates that I'm trackun using the COUNTIF function based on the dates that are 91-180, 181-270, & 271-365, days old, based on the TODAY date. for the 90-180days, I'm using the following, but doesn't pan out: =COUNTIF(MASTER!AI5:AI5997,"="&TODAY()-180), (MASTER!AI5:AI5997,"<="&TODAY()-90) Any help would be graetly appreciated!! Keith |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use multiple criteria with COUNTIF: between dates and not bla
Thanks much...That works great.
My dates are color coded, so how can I use the same formula, but only count the dates in a specific color? Can this even be done? Best regards -- Keith "T. Valko" wrote: Try this: =SUMPRODUCT(--(MASTER!AI5:AI5997=TODAY()-180),--(MASTER!AI5:AI5997<=TODAY()-91)) -- Biff Microsoft Excel MVP "Nascarfan88" wrote in message ... I have a similar issue: I have a range of dates that I'm trackun using the COUNTIF function based on the dates that are 91-180, 181-270, & 271-365, days old, based on the TODAY date. for the 90-180days, I'm using the following, but doesn't pan out: =COUNTIF(MASTER!AI5:AI5997,"="&TODAY()-180), (MASTER!AI5:AI5997,"<="&TODAY()-90) Any help would be graetly appreciated!! Keith |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use multiple criteria with COUNTIF: between dates and not bla
It can be done but it's complicated and it might not work the way you think
it should work. I recommend not doing it that way. See this: http://www.cpearson.com/Excel/colors.aspx -- Biff Microsoft Excel MVP "Nascarfan88" wrote in message ... Thanks much...That works great. My dates are color coded, so how can I use the same formula, but only count the dates in a specific color? Can this even be done? Best regards -- Keith "T. Valko" wrote: Try this: =SUMPRODUCT(--(MASTER!AI5:AI5997=TODAY()-180),--(MASTER!AI5:AI5997<=TODAY()-91)) -- Biff Microsoft Excel MVP "Nascarfan88" wrote in message ... I have a similar issue: I have a range of dates that I'm trackun using the COUNTIF function based on the dates that are 91-180, 181-270, & 271-365, days old, based on the TODAY date. for the 90-180days, I'm using the following, but doesn't pan out: =COUNTIF(MASTER!AI5:AI5997,"="&TODAY()-180), (MASTER!AI5:AI5997,"<="&TODAY()-90) Any help would be graetly appreciated!! Keith |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use multiple criteria with COUNTIF: between dates and not bla
Wow...I agree...I'll figure something else.
Thanks very much for your help! -- Keith "T. Valko" wrote: It can be done but it's complicated and it might not work the way you think it should work. I recommend not doing it that way. See this: http://www.cpearson.com/Excel/colors.aspx -- Biff Microsoft Excel MVP "Nascarfan88" wrote in message ... Thanks much...That works great. My dates are color coded, so how can I use the same formula, but only count the dates in a specific color? Can this even be done? Best regards -- Keith "T. Valko" wrote: Try this: =SUMPRODUCT(--(MASTER!AI5:AI5997=TODAY()-180),--(MASTER!AI5:AI5997<=TODAY()-91)) -- Biff Microsoft Excel MVP "Nascarfan88" wrote in message ... I have a similar issue: I have a range of dates that I'm trackun using the COUNTIF function based on the dates that are 91-180, 181-270, & 271-365, days old, based on the TODAY date. for the 90-180days, I'm using the following, but doesn't pan out: =COUNTIF(MASTER!AI5:AI5997,"="&TODAY()-180), (MASTER!AI5:AI5997,"<="&TODAY()-90) Any help would be graetly appreciated!! Keith |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use multiple criteria with COUNTIF: between dates and not bla
See if this gives you an idea...
Your dates are color coded for a reason. Use that reason as the logic in a formula. If they're color coded using conditional formatting, use the same logic of the conditional formatting rule to write a formula. -- Biff Microsoft Excel MVP "Nascarfan88" wrote in message ... Wow...I agree...I'll figure something else. Thanks very much for your help! -- Keith "T. Valko" wrote: It can be done but it's complicated and it might not work the way you think it should work. I recommend not doing it that way. See this: http://www.cpearson.com/Excel/colors.aspx -- Biff Microsoft Excel MVP "Nascarfan88" wrote in message ... Thanks much...That works great. My dates are color coded, so how can I use the same formula, but only count the dates in a specific color? Can this even be done? Best regards -- Keith "T. Valko" wrote: Try this: =SUMPRODUCT(--(MASTER!AI5:AI5997=TODAY()-180),--(MASTER!AI5:AI5997<=TODAY()-91)) -- Biff Microsoft Excel MVP "Nascarfan88" wrote in message ... I have a similar issue: I have a range of dates that I'm trackun using the COUNTIF function based on the dates that are 91-180, 181-270, & 271-365, days old, based on the TODAY date. for the 90-180days, I'm using the following, but doesn't pan out: =COUNTIF(MASTER!AI5:AI5997,"="&TODAY()-180), (MASTER!AI5:AI5997,"<="&TODAY()-90) Any help would be graetly appreciated!! Keith |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|