Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I....
I need to count the number of cells that have dates in them based on the
month which is listed in column A (months are listed as sept, oct, Nov) How can I do this?? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I....
Not sure what exactly you require but ....
=SUMPRODUCT(--(month(B2:B1000)=9)) will give number of dates in B which are September HTH "TDW" wrote: I need to count the number of cells that have dates in them based on the month which is listed in column A (months are listed as sept, oct, Nov) How can I do this?? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I....
try this -
I created a simple spreadsheet as follows: col A is the date 1/1/07 1/2/07... col. B is a helper column to get the month =month(a1) Somewhere on the spreadsheet create a lookup table so that you can convert the month from Jan, Feb... format to 1, 2, ... I did it in F2:G13 F1 = Jan G1 = 1 F2 = Feb G2 = 2... Assume you enter your first date in Cell A1, and the month you want to count is in G23... then the following formula will get your answer. =countif($b1:$B26, vlookup(G23,$f$2:$g$13,2,false)) On Aug 15, 4:18 pm, TDW wrote: I need to count the number of cells that have dates in them based on the month which is listed in column A (months are listed as sept, oct, Nov) How can I do this?? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I....
Hi,
countif(A1:A100,"sept") countif(A1:A100,"oct") countif(A1:A100,"nov") Thanks, -- Farhad Hodjat "TDW" wrote: I need to count the number of cells that have dates in them based on the month which is listed in column A (months are listed as sept, oct, Nov) How can I do this?? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I....
Topper: I think you got it close. We need to convert sept to the number 9.
Putting it in a datevalue function will get this result. =SUMPRODUCT(--(MONTH(B2:B1000)=MONTH(DATEVALUE("1-"&A1&"-07")))) "Toppers" wrote: Not sure what exactly you require but .... =SUMPRODUCT(--(month(B2:B1000)=9)) will give number of dates in B which are September HTH "TDW" wrote: I need to count the number of cells that have dates in them based on the month which is listed in column A (months are listed as sept, oct, Nov) How can I do this?? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I....
You can simplify that by using
=SUMPRODUCT(--(TEXT(B2:B1000,"mmm")=A1)) or to guard against if A1 could be either Sep or September =SUMPRODUCT(--(TEXT(B2:B1000,REPT("m",MIN(4,LEN(A1))))=A1)) note that blank cells score as January so to test for a numerical date in B might be a good idea =SUMPRODUCT(--(TEXT(B2:B1000,REPT("m",MIN(4,LEN(A1))))=A1),--(ISNUMBER(B2:B1000))) -- Regards, Peo Sjoblom "Joel" wrote in message ... Topper: I think you got it close. We need to convert sept to the number 9. Putting it in a datevalue function will get this result. =SUMPRODUCT(--(MONTH(B2:B1000)=MONTH(DATEVALUE("1-"&A1&"-07")))) "Toppers" wrote: Not sure what exactly you require but .... =SUMPRODUCT(--(month(B2:B1000)=9)) will give number of dates in B which are September HTH "TDW" wrote: I need to count the number of cells that have dates in them based on the month which is listed in column A (months are listed as sept, oct, Nov) How can I do this?? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I....
You code won't work because sept is four letters and your code only produces
3 letters. Sept will not compare. the date value function will convert both SEP and SEPT to the month number. "Peo Sjoblom" wrote: You can simplify that by using =SUMPRODUCT(--(TEXT(B2:B1000,"mmm")=A1)) or to guard against if A1 could be either Sep or September =SUMPRODUCT(--(TEXT(B2:B1000,REPT("m",MIN(4,LEN(A1))))=A1)) note that blank cells score as January so to test for a numerical date in B might be a good idea =SUMPRODUCT(--(TEXT(B2:B1000,REPT("m",MIN(4,LEN(A1))))=A1),--(ISNUMBER(B2:B1000))) -- Regards, Peo Sjoblom "Joel" wrote in message ... Topper: I think you got it close. We need to convert sept to the number 9. Putting it in a datevalue function will get this result. =SUMPRODUCT(--(MONTH(B2:B1000)=MONTH(DATEVALUE("1-"&A1&"-07")))) "Toppers" wrote: Not sure what exactly you require but .... =SUMPRODUCT(--(month(B2:B1000)=9)) will give number of dates in B which are September HTH "TDW" wrote: I need to count the number of cells that have dates in them based on the month which is listed in column A (months are listed as sept, oct, Nov) How can I do this?? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I....
Why would anyone use Sept, does anyone use Octo or Nove?
Of course that can be easily fixed =SUMPRODUCT(--(TEXT(B2:B1000,"mmm")=LEFT(A1,3))) =SUMPRODUCT(--(TEXT(B2:B1000,"mmm")=LEFT(A1,3)),--(ISNUMBER(B2:B1000))) Regardless your formula will return an erroneous result if there are blanks in B2:B1000 and DATEVALUE is a totally obsolete function =SUMPRODUCT(--(MONTH(B2:B1000)=MONTH(--("1-"&A1&"-07")))) or to fix the blank cell errors =SUMPRODUCT(--(MONTH(B2:B1000)=MONTH(--("1-"&A1&"-07")--(ISNUMBER(B2:B1000))))) HTH -- Regards, Peo Sjoblom "Joel" wrote in message ... You code won't work because sept is four letters and your code only produces 3 letters. Sept will not compare. the date value function will convert both SEP and SEPT to the month number. "Peo Sjoblom" wrote: You can simplify that by using =SUMPRODUCT(--(TEXT(B2:B1000,"mmm")=A1)) or to guard against if A1 could be either Sep or September =SUMPRODUCT(--(TEXT(B2:B1000,REPT("m",MIN(4,LEN(A1))))=A1)) note that blank cells score as January so to test for a numerical date in B might be a good idea =SUMPRODUCT(--(TEXT(B2:B1000,REPT("m",MIN(4,LEN(A1))))=A1),--(ISNUMBER(B2:B1000))) -- Regards, Peo Sjoblom "Joel" wrote in message ... Topper: I think you got it close. We need to convert sept to the number 9. Putting it in a datevalue function will get this result. =SUMPRODUCT(--(MONTH(B2:B1000)=MONTH(DATEVALUE("1-"&A1&"-07")))) "Toppers" wrote: Not sure what exactly you require but .... =SUMPRODUCT(--(month(B2:B1000)=9)) will give number of dates in B which are September HTH "TDW" wrote: I need to count the number of cells that have dates in them based on the month which is listed in column A (months are listed as sept, oct, Nov) How can I do this?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|