Counting Dates that fall within a certain month

**wnstar21** · 01-23-2009, 04:56 PM

Please open the xls. file I've attached to help understand me problem.

I have items arriving on certain dates (the dates are listed in column N)

In the cell to the right of each month, I want to have a formula that will count the dated cells in column N that fall under each month.

i.e. In cell B5, next to January, I want to display the total number of cells that contain a date in January (ultimately giving me the number of items that arrive in January) the number would read '5' because there are 5 dates in January that are in the list.

How can I accomplish this?

**NBVC** · 01-23-2009, 05:01 PM

Try:

=SUMPRODUCT(--(TEXT($N$7:$N$35,"mmmm")=A5))

copied down

adjust range to suit.

**jasoncw** · 01-23-2009, 05:05 PM

You can use this for Jan, then copy down for the rest of the months:

=SUMPRODUCT(--(MONTH(N$7:N$35)=ROW(A1)))

This will only look for the month, regardless of the year. In other words, it will count it if it is Jan-08 or Jan-09. Is that ok?

Jason

**wnstar21** · 01-23-2009, 05:15 PM

I will only be using it for 5-6 month periods, at the most. So the year won't matter. I will let you know if this works.

Thank you

**wnstar21** · 01-23-2009, 05:20 PM

It works!

But can I ask someone to interpret this formula?

What does the '--' mean after sumproduct?

What does the formula have 'A1' in it for January, when January is in cell A5?

**wnstar21** · 01-23-2009, 05:35 PM

I don't know why, but whem I change the range to the real range I will be using (in another Excel file). All of the months work, except for January.

The range is from R72:R250. And January returns '171', which I can't figure out how

So close, please help!

**jasoncw** · 01-23-2009, 05:57 PM

Which formula are you using?

Here is a good write-up of how Sumproduct works:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

**martindwilson** · 01-23-2009, 08:01 PM

put year in a1
then
=SUMPRODUCT(($N$7:$N$35>=DATE($A$1,1,1))*($N$7:$N$35<=DATE($A$1,1,31)))
will give total for jan of year in a1
it gets a tad more complicate if you want to auto increment on drag, and then more fun if you want to span 2008-2009!

**NBVC** · 01-23-2009, 09:13 PM

Originally Posted by wnstar21

It works!

But can I ask someone to interpret this formula?

What does the '--' mean after sumproduct?

What does the formula have 'A1' in it for January, when January is in cell A5?

If you have blank cells, the sumproduct formulas given will interpret them as have "January" because the blanks are treated as 0's and the 0 in date serial number translates to January 0, 1900...

so you need to add another condition to prevent the blanks from being counted...

try:

Please Login or Register  to view this content.

Where A5 contains the word January.... change as necessary and copy down.

**wnstar21** · 01-26-2009, 12:24 PM

The last formula works. Thank you very much!

Also, the SUMPRODUCT article doesn't show up?

Counting Dates that fall within a certain month

LinkBack

Thread Tools

Rate This Thread

Display

Counting Dates that fall within a certain month

Re: Counting Dates that fall within a certain month

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions