Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting dates based on certain criteria
I have a col of dates (D1:D250) and another column (B1:B250) with 3 different
options. I want to know how many times option 1 happened when the month is Jan, Fec, etc. option A 5/8/2007 option B 4/10/2007 option B 5/11/2007 Thanks in advance for you help... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting dates based on certain criteria
For OptionA and January, try:
=Sumproduct(--(B1:B250="OptionA"), --(Month(D1:D250)=1)) do you need to differentiate between Jan 2007 and Jan 2008 or just match to Jan regardless of the year?? "steve_sr2" wrote: I have a col of dates (D1:D250) and another column (B1:B250) with 3 different options. I want to know how many times option 1 happened when the month is Jan, Fec, etc. option A 5/8/2007 option B 4/10/2007 option B 5/11/2007 Thanks in advance for you help... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting dates based on certain criteria
When I use this, I get an error #VALUE
"JMB" wrote: For OptionA and January, try: =Sumproduct(--(B1:B250="OptionA"), --(Month(D1:D250)=1)) do you need to differentiate between Jan 2007 and Jan 2008 or just match to Jan regardless of the year?? "steve_sr2" wrote: I have a col of dates (D1:D250) and another column (B1:B250) with 3 different options. I want to know how many times option 1 happened when the month is Jan, Fec, etc. option A 5/8/2007 option B 4/10/2007 option B 5/11/2007 Thanks in advance for you help... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting dates based on certain criteria
When I use this, I get an error #VALUE
That usually means you have #VALUE! error value(s) within your data, either in col B and/or col D, or col D might contain some cells with text (instead of real dates/numbers). Check it and clear these cells. Above said, perhaps a more unambiguous/robust summation to try, as hinted in JMB's question to you would be something like this: =SUMPRODUCT(--(B1:B250="Option A"), --(TEXT(D1:D250,"mmmyyyy")="Aug2007")) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting dates based on certain criteria
Thanks Max, the option worked...
"Max" wrote: When I use this, I get an error #VALUE That usually means you have #VALUE! error value(s) within your data, either in col B and/or col D, or col D might contain some cells with text (instead of real dates/numbers). Check it and clear these cells. Above said, perhaps a more unambiguous/robust summation to try, as hinted in JMB's question to you would be something like this: =SUMPRODUCT(--(B1:B250="Option A"), --(TEXT(D1:D250,"mmmyyyy")="Aug2007")) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting dates based on certain criteria
welcome, glad it helped
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "steve_sr2" wrote in message ... Thanks Max, the option worked... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting based on criteria from two cells??? | Excel Worksheet Functions | |||
Counting unique dates based on selected criteria in a list | Excel Worksheet Functions | |||
Counting based on multiple criteria | Excel Discussion (Misc queries) | |||
counting occurences based on two criteria | Excel Discussion (Misc queries) | |||
counting based on criteria | Excel Worksheet Functions |