How to count the number of times something occurs within a certain month
Thanks Roger,
It works great for all the months except for Jan. I used the following formula for Jan and instead of returning 3 it returned 77. =SUMPRODUCT(--(MONTH($B$1:$B$100)=1)) I'm not sure why, if you have any ideas please let me know. Thanks, Joyce From: "Roger Govier" To: "Joyce" Subject: How to count the number of times something occurs within a certain month Date: Tuesday, October 18, 2005 10:11 AM Hi Joyce One way =SUMPRODUCT(--(MONTH($A$1:$A$100)=2) for February Change to 3 for March etc. or put the Month number required in a cell and refer to the cell =SUMPRODUCT(--(MONTH($A$1:$A$100)=B1) Regards Roger Govier Joyce wrote: Hi, Does anyone have a suggestion on how I should do the following? I have a table column that contains dates like, 1 Feb 05 6 Feb 05 27 Feb 05 7 Mar 05 20 Mar 05 I want to be able to count Feb = 3 and Mar = 2. Thanks! Joyce |
How to count the number of times something occurs within a cer
Blanks and zeros will come up as "1" for the month() figure
if you have non dates if would use =SUMPRODUCT(--(MONTH($B$1:$B$100)=1),--($B$1:$B$10036525)) if all of your dates are later than 1999. "Joyce" wrote: Thanks Roger, It works great for all the months except for Jan. I used the following formula for Jan and instead of returning 3 it returned 77. =SUMPRODUCT(--(MONTH($B$1:$B$100)=1)) I'm not sure why, if you have any ideas please let me know. Thanks, Joyce From: "Roger Govier" To: "Joyce" Subject: How to count the number of times something occurs within a certain month Date: Tuesday, October 18, 2005 10:11 AM Hi Joyce One way =SUMPRODUCT(--(MONTH($A$1:$A$100)=2) for February Change to 3 for March etc. or put the Month number required in a cell and refer to the cell =SUMPRODUCT(--(MONTH($A$1:$A$100)=B1) Regards Roger Govier Joyce wrote: Hi, Does anyone have a suggestion on how I should do the following? I have a table column that contains dates like, 1 Feb 05 6 Feb 05 27 Feb 05 7 Mar 05 20 Mar 05 I want to be able to count Feb = 3 and Mar = 2. Thanks! Joyce |
How to count the number of times something occurs within a certainmonth
Hi Joyce
I guess some of the cells in the range were blank. Excel would have treated them as being 01 Jan 1900, and therefore counted them in with the month 1 total We can add another condition to the formula =SUMPRODUCT(--(MONTH($B$1:$B$100)=1,--($B$1:$B$100<"")) which will test for null values. Regards Roger Govier Joyce wrote: Thanks Roger, It works great for all the months except for Jan. I used the following formula for Jan and instead of returning 3 it returned 77. =SUMPRODUCT(--(MONTH($B$1:$B$100)=1)) I'm not sure why, if you have any ideas please let me know. Thanks, Joyce From: "Roger Govier" To: "Joyce" Subject: How to count the number of times something occurs within a certain month Date: Tuesday, October 18, 2005 10:11 AM Hi Joyce One way =SUMPRODUCT(--(MONTH($A$1:$A$100)=2) for February Change to 3 for March etc. or put the Month number required in a cell and refer to the cell =SUMPRODUCT(--(MONTH($A$1:$A$100)=B1) Regards Roger Govier Joyce wrote: Hi, Does anyone have a suggestion on how I should do the following? I have a table column that contains dates like, 1 Feb 05 6 Feb 05 27 Feb 05 7 Mar 05 20 Mar 05 I want to be able to count Feb = 3 and Mar = 2. Thanks! Joyce |
All times are GMT +1. The time now is 03:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com