| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF between dates and if value = "y"
I have been using the COUNTIF function to count the occurences of dates that
fall between a particular set of dates. Is there a way to count these dates, only if a cell on the same row as the counted date equals 'Y" This is the formula that I have been using to count occurences between Dec.31-06 and Dec.31-07. =COUNTIF(A1:A5,"<=12/31/07")-COUNTIF(A1:A5,"<=12/31/06") Can I add to this in order to count the cells that also contain a "y" on the same row? Column A are the dates, Column B are the Y or N values. A B 1 Sept-08-06 Y 2 Oct-24-05 N 3 Jul-21-07 N 4 Aug-10-07 Y 5 Jan-01-05 Y The date COUNTIF formula shown above would result in 2. (A3 and A4) The result I am looking for (with the "y") should be 1. (A4 only) Any ideas ? Thx. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF between dates and if value = "y"
=SUMPRODUCT(--(YEAR(A1:A5)=2007),--(B1:B5="Y"))
-- Regards, Peo Sjoblom "JLV" wrote in message ... I have been using the COUNTIF function to count the occurences of dates that fall between a particular set of dates. Is there a way to count these dates, only if a cell on the same row as the counted date equals 'Y" This is the formula that I have been using to count occurences between Dec.31-06 and Dec.31-07. =COUNTIF(A1:A5,"<=12/31/07")-COUNTIF(A1:A5,"<=12/31/06") Can I add to this in order to count the cells that also contain a "y" on the same row? Column A are the dates, Column B are the Y or N values. A B 1 Sept-08-06 Y 2 Oct-24-05 N 3 Jul-21-07 N 4 Aug-10-07 Y 5 Jan-01-05 Y The date COUNTIF formula shown above would result in 2. (A3 and A4) The result I am looking for (with the "y") should be 1. (A4 only) Any ideas ? Thx. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF between dates and if value = "y"
Try this:
=SUMPRODUCT(--(A1:A5=DATE(2006,12,31)),--(A1:A5<=DATE(2007,12,31)),--(B1:B5="Y")) Better to use cells to hold the criteria. D1 = start date E1 = end date F1 = Y =SUMPRODUCT(--(A1:A5=D1),--(A1:A5<=E1),--(B1:B5=F1)) -- Biff Microsoft Excel MVP "JLV" wrote in message ... I have been using the COUNTIF function to count the occurences of dates that fall between a particular set of dates. Is there a way to count these dates, only if a cell on the same row as the counted date equals 'Y" This is the formula that I have been using to count occurences between Dec.31-06 and Dec.31-07. =COUNTIF(A1:A5,"<=12/31/07")-COUNTIF(A1:A5,"<=12/31/06") Can I add to this in order to count the cells that also contain a "y" on the same row? Column A are the dates, Column B are the Y or N values. A B 1 Sept-08-06 Y 2 Oct-24-05 N 3 Jul-21-07 N 4 Aug-10-07 Y 5 Jan-01-05 Y The date COUNTIF formula shown above would result in 2. (A3 and A4) The result I am looking for (with the "y") should be 1. (A4 only) Any ideas ? Thx. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF between dates and if value = "y"
I think that 12/31/06 should be excluded given the countif formula the OP
posted, that means it can be done using YEAR -- Regards, Peo Sjoblom "T. Valko" wrote in message ... Try this: =SUMPRODUCT(--(A1:A5=DATE(2006,12,31)),--(A1:A5<=DATE(2007,12,31)),--(B1:B5="Y")) Better to use cells to hold the criteria. D1 = start date E1 = end date F1 = Y =SUMPRODUCT(--(A1:A5=D1),--(A1:A5<=E1),--(B1:B5=F1)) -- Biff Microsoft Excel MVP "JLV" wrote in message ... I have been using the COUNTIF function to count the occurences of dates that fall between a particular set of dates. Is there a way to count these dates, only if a cell on the same row as the counted date equals 'Y" This is the formula that I have been using to count occurences between Dec.31-06 and Dec.31-07. =COUNTIF(A1:A5,"<=12/31/07")-COUNTIF(A1:A5,"<=12/31/06") Can I add to this in order to count the cells that also contain a "y" on the same row? Column A are the dates, Column B are the Y or N values. A B 1 Sept-08-06 Y 2 Oct-24-05 N 3 Jul-21-07 N 4 Aug-10-07 Y 5 Jan-01-05 Y The date COUNTIF formula shown above would result in 2. (A3 and A4) The result I am looking for (with the "y") should be 1. (A4 only) Any ideas ? Thx. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF between dates and if value = "y"
Yeah, when I see a posted formula like:
=COUNTIF(A1:A5,"<=12/31/07")-COUNTIF(A1:A5,"<=12/31/06") I always assume (for better or worse!!!) they meant = and <=. You know what they say about assuming! -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... I think that 12/31/06 should be excluded given the countif formula the OP posted, that means it can be done using YEAR -- Regards, Peo Sjoblom "T. Valko" wrote in message ... Try this: =SUMPRODUCT(--(A1:A5=DATE(2006,12,31)),--(A1:A5<=DATE(2007,12,31)),--(B1:B5="Y")) Better to use cells to hold the criteria. D1 = start date E1 = end date F1 = Y =SUMPRODUCT(--(A1:A5=D1),--(A1:A5<=E1),--(B1:B5=F1)) -- Biff Microsoft Excel MVP "JLV" wrote in message ... I have been using the COUNTIF function to count the occurences of dates that fall between a particular set of dates. Is there a way to count these dates, only if a cell on the same row as the counted date equals 'Y" This is the formula that I have been using to count occurences between Dec.31-06 and Dec.31-07. =COUNTIF(A1:A5,"<=12/31/07")-COUNTIF(A1:A5,"<=12/31/06") Can I add to this in order to count the cells that also contain a "y" on the same row? Column A are the dates, Column B are the Y or N values. A B 1 Sept-08-06 Y 2 Oct-24-05 N 3 Jul-21-07 N 4 Aug-10-07 Y 5 Jan-01-05 Y The date COUNTIF formula shown above would result in 2. (A3 and A4) The result I am looking for (with the "y") should be 1. (A4 only) Any ideas ? Thx. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF between dates and if value = "y"
This works quite nicely. Thanks very much.
However, I will need to swap out my COUNTIF formula to the SUMPRODUCT formula shown below in order to keep my values consistant. =SUMPRODUCT(--(A1:A5=DATE(2006,12,31)),--(A1:A5<=DATE(2007,12,31))) All I did was remove the "Y" array. Thx again, JLV "T. Valko" wrote: Try this: =SUMPRODUCT(--(A1:A5=DATE(2006,12,31)),--(A1:A5<=DATE(2007,12,31)),--(B1:B5="Y")) Better to use cells to hold the criteria. D1 = start date E1 = end date F1 = Y =SUMPRODUCT(--(A1:A5=D1),--(A1:A5<=E1),--(B1:B5=F1)) -- Biff Microsoft Excel MVP "JLV" wrote in message ... I have been using the COUNTIF function to count the occurences of dates that fall between a particular set of dates. Is there a way to count these dates, only if a cell on the same row as the counted date equals 'Y" This is the formula that I have been using to count occurences between Dec.31-06 and Dec.31-07. =COUNTIF(A1:A5,"<=12/31/07")-COUNTIF(A1:A5,"<=12/31/06") Can I add to this in order to count the cells that also contain a "y" on the same row? Column A are the dates, Column B are the Y or N values. A B 1 Sept-08-06 Y 2 Oct-24-05 N 3 Jul-21-07 N 4 Aug-10-07 Y 5 Jan-01-05 Y The date COUNTIF formula shown above would result in 2. (A3 and A4) The result I am looking for (with the "y") should be 1. (A4 only) Any ideas ? Thx. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF between dates and if value = "y"
Hello,
Just some clarification... The formula below subtracts all of the dates that are <= 12/31/06 from all dates that are <= 12/31/07. This will give me a count of all the dates from 01/01/07 to 12/31/07. I wasn't aware that a conditional like " <= and = " could be used in a COUNTIF. If it can be....great. "T. Valko" wrote: Yeah, when I see a posted formula like: =COUNTIF(A1:A5,"<=12/31/07")-COUNTIF(A1:A5,"<=12/31/06") I always assume (for better or worse!!!) they meant = and <=. You know what they say about assuming! -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... I think that 12/31/06 should be excluded given the countif formula the OP posted, that means it can be done using YEAR -- Regards, Peo Sjoblom "T. Valko" wrote in message ... Try this: =SUMPRODUCT(--(A1:A5=DATE(2006,12,31)),--(A1:A5<=DATE(2007,12,31)),--(B1:B5="Y")) Better to use cells to hold the criteria. D1 = start date E1 = end date F1 = Y =SUMPRODUCT(--(A1:A5=D1),--(A1:A5<=E1),--(B1:B5=F1)) -- Biff Microsoft Excel MVP "JLV" wrote in message ... I have been using the COUNTIF function to count the occurences of dates that fall between a particular set of dates. Is there a way to count these dates, only if a cell on the same row as the counted date equals 'Y" This is the formula that I have been using to count occurences between Dec.31-06 and Dec.31-07. =COUNTIF(A1:A5,"<=12/31/07")-COUNTIF(A1:A5,"<=12/31/06") Can I add to this in order to count the cells that also contain a "y" on the same row? Column A are the dates, Column B are the Y or N values. A B 1 Sept-08-06 Y 2 Oct-24-05 N 3 Jul-21-07 N 4 Aug-10-07 Y 5 Jan-01-05 Y The date COUNTIF formula shown above would result in 2. (A3 and A4) The result I am looking for (with the "y") should be 1. (A4 only) Any ideas ? Thx. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF between dates and if value = "y"
What's wrong with using the YEAR on one array instead of = and <= on 2
arrays? -- Regards, Peo Sjoblom "JohnLVand" wrote in message ... Hello, Just some clarification... The formula below subtracts all of the dates that are <= 12/31/06 from all dates that are <= 12/31/07. This will give me a count of all the dates from 01/01/07 to 12/31/07. I wasn't aware that a conditional like " <= and = " could be used in a COUNTIF. If it can be....great. "T. Valko" wrote: Yeah, when I see a posted formula like: =COUNTIF(A1:A5,"<=12/31/07")-COUNTIF(A1:A5,"<=12/31/06") I always assume (for better or worse!!!) they meant = and <=. You know what they say about assuming! -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... I think that 12/31/06 should be excluded given the countif formula the OP posted, that means it can be done using YEAR -- Regards, Peo Sjoblom "T. Valko" wrote in message ... Try this: =SUMPRODUCT(--(A1:A5=DATE(2006,12,31)),--(A1:A5<=DATE(2007,12,31)),--(B1:B5="Y")) Better to use cells to hold the criteria. D1 = start date E1 = end date F1 = Y =SUMPRODUCT(--(A1:A5=D1),--(A1:A5<=E1),--(B1:B5=F1)) -- Biff Microsoft Excel MVP "JLV" wrote in message ... I have been using the COUNTIF function to count the occurences of dates that fall between a particular set of dates. Is there a way to count these dates, only if a cell on the same row as the counted date equals 'Y" This is the formula that I have been using to count occurences between Dec.31-06 and Dec.31-07. =COUNTIF(A1:A5,"<=12/31/07")-COUNTIF(A1:A5,"<=12/31/06") Can I add to this in order to count the cells that also contain a "y" on the same row? Column A are the dates, Column B are the Y or N values. A B 1 Sept-08-06 Y 2 Oct-24-05 N 3 Jul-21-07 N 4 Aug-10-07 Y 5 Jan-01-05 Y The date COUNTIF formula shown above would result in 2. (A3 and A4) The result I am looking for (with the "y") should be 1. (A4 only) Any ideas ? Thx. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF between dates and if value = "y"
I misinterpreted your intentions. Since all you want to do is count entries
for the year 2007 Peo's suggestion is the best solution. I understand the logic of subtracting <= and <= but it seems more intuitive (to me) to use = and <= -- Biff Microsoft Excel MVP "JohnLVand" wrote in message ... This works quite nicely. Thanks very much. However, I will need to swap out my COUNTIF formula to the SUMPRODUCT formula shown below in order to keep my values consistant. =SUMPRODUCT(--(A1:A5=DATE(2006,12,31)),--(A1:A5<=DATE(2007,12,31))) All I did was remove the "Y" array. Thx again, JLV "T. Valko" wrote: Try this: =SUMPRODUCT(--(A1:A5=DATE(2006,12,31)),--(A1:A5<=DATE(2007,12,31)),--(B1:B5="Y")) Better to use cells to hold the criteria. D1 = start date E1 = end date F1 = Y =SUMPRODUCT(--(A1:A5=D1),--(A1:A5<=E1),--(B1:B5=F1)) -- Biff Microsoft Excel MVP "JLV" wrote in message ... I have been using the COUNTIF function to count the occurences of dates that fall between a particular set of dates. Is there a way to count these dates, only if a cell on the same row as the counted date equals 'Y" This is the formula that I have been using to count occurences between Dec.31-06 and Dec.31-07. =COUNTIF(A1:A5,"<=12/31/07")-COUNTIF(A1:A5,"<=12/31/06") Can I add to this in order to count the cells that also contain a "y" on the same row? Column A are the dates, Column B are the Y or N values. A B 1 Sept-08-06 Y 2 Oct-24-05 N 3 Jul-21-07 N 4 Aug-10-07 Y 5 Jan-01-05 Y The date COUNTIF formula shown above would result in 2. (A3 and A4) The result I am looking for (with the "y") should be 1. (A4 only) Any ideas ? Thx. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
| HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| what is syntax for if(between range of dates,"Q1","Q2")? | Excel Worksheet Functions | |||
| Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) | |||
Linear Mode
