Counting Dates-total number of transactions processed

I have two spreadsheets, one containg data the other the results. In my results spreadsheet, I want to come up with the total number of transactions processed for a specific month. The formula I am using is =COUNTIF('2005'!$E$2:$E$10000, A5). A5 being Jan 1, 2005. However, the formula only seems to be looking for transations processed on Jan 1, 2005 and not for the entire month of Jan. Is there a formula I can use to solve this issue?? Thanks!

ROSE2102 wrote:
> I have two spreadsheets, one containg data the other the results. In my
> results spreadsheet, I want to come up with the total number of
> transactions processed for a specific month. The formula I am using is
> =COUNTIF('2005'!$E$2:$E$10000, A5). A5 being Jan 1, 2005. However, the
> formula only seems to be looking for transations processed on Jan 1,
> 2005 and not for the entire month of Jan. Is there a formula I can use
> to solve this issue?? Thanks!

Yes, you need to enter this an an array formula. To get more of an
understanding on what that is, look in the help for excel. Here's a
formula you can adapt

=SUM(IF(MONTH(A1:A8)=9,1,0))

When you enter the formula, you need to hold down CTRL+SHIFT+ENTER.
This makes it an array formula

a1:a8 is your range, =9 is your month (in this example, september)

So what this does it checks the month of each cell from a1.a8. If it
=9, it returns a "1" which is then summed.

There's probably other variations that you can use, but this is easy.

=SUMPRODUCT(--(TEXT('2005'!$E2:$E$10000,"YYYYMM")="200501"))

If you ever decide to just check for month, be aware that empty cells will
return 1:

with a1 empty:
=month(a1)
will return 1.



ROSE2102 wrote:
>
> I have two spreadsheets, one containg data the other the results. In my
> results spreadsheet, I want to come up with the total number of
> transactions processed for a specific month. The formula I am using is
> =COUNTIF('2005'!$E$2:$E$10000, A5). A5 being Jan 1, 2005. However, the
> formula only seems to be looking for transations processed on Jan 1,
> 2005 and not for the entire month of Jan. Is there a formula I can use
> to solve this issue?? Thanks!
>
> --
> ROSE2102
> ------------------------------------------------------------------------
> ROSE2102's Profile: http://www.excelforum.com/member.php...fo&userid=9202
> View this thread: http://www.excelforum.com/showthread...hreadid=494125

--

Dave Peterson