Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting dates using SUMPRODUCT
I have a column with 100 rows that will eventually contain a publication
date. Those cells are formatted as dates. Some of the cells in the column have dates and I need to count the number of publication dates in July, August, etc. I'm using the formula =SUMPRODUCT(--(MONTH(A1:A100)=7)) [7 being the number that represents July] and the formula yields the number of publication dates in July. My problem is that the formula is counting every blank cell as a January date. Any help you can give is greatly appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting dates using SUMPRODUCT
On Mon, 27 Jul 2009 15:52:01 -0700, Marge
wrote: I have a column with 100 rows that will eventually contain a publication date. Those cells are formatted as dates. Some of the cells in the column have dates and I need to count the number of publication dates in July, August, etc. I'm using the formula =SUMPRODUCT(--(MONTH(A1:A100)=7)) [7 being the number that represents July] and the formula yields the number of publication dates in July. My problem is that the formula is counting every blank cell as a January date. Any help you can give is greatly appreciated. Try modifying your formula for January like this: =SUMPRODUCT((MONTH(A1:A100)=1)*(A1:A100<"")) Hope this helps / Lars-Åke |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting dates using SUMPRODUCT
This way, using both month/year would always be unambiguous, eg:
=SUMPRODUCT(--(TEXT(A1:A100,"mmmyy")="Jan09")) -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Marge" wrote: I have a column with 100 rows that will eventually contain a publication date. Those cells are formatted as dates. Some of the cells in the column have dates and I need to count the number of publication dates in July, August, etc. I'm using the formula =SUMPRODUCT(--(MONTH(A1:A100)=7)) [7 being the number that represents July] and the formula yields the number of publication dates in July. My problem is that the formula is counting every blank cell as a January date. Any help you can give is greatly appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting dates using SUMPRODUCT
Thank you! What I actually did was add a string to count the Januarys
greater than 2000 and it worked fine. Thanks again for your help. "Max" wrote: This way, using both month/year would always be unambiguous, eg: =SUMPRODUCT(--(TEXT(A1:A100,"mmmyy")="Jan09")) -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Marge" wrote: I have a column with 100 rows that will eventually contain a publication date. Those cells are formatted as dates. Some of the cells in the column have dates and I need to count the number of publication dates in July, August, etc. I'm using the formula =SUMPRODUCT(--(MONTH(A1:A100)=7)) [7 being the number that represents July] and the formula yields the number of publication dates in July. My problem is that the formula is counting every blank cell as a January date. Any help you can give is greatly appreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting dates using SUMPRODUCT
Welcome, glad you got going there
-- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Marge" wrote in message ... Thank you! What I actually did was add a string to count the Januarys greater than 2000 and it worked fine. Thanks again for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting non blanks with SUMPRODUCT? | Excel Discussion (Misc queries) | |||
counting occasions dates occur between 2 dates | New Users to Excel | |||
Counting dates, within a list of dates | Excel Worksheet Functions | |||
do I need array formula or sumproduct for counting? | Excel Worksheet Functions | |||
sumproduct--counting--zero--blank cells | Excel Discussion (Misc queries) |