Counting Dates

R

ROSE2102

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!
 
L

Larry Bud

ROSE2102 said:
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!
Click to expand...

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.
 
D

Dave Peterson

=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.
 
Top