Countif Sumproduct

[gjameson via OfficeKB.com]

I just want to count the occurrences for any given month in the year. How
would you set
up the date range?

I have tried =SUMPRODUCT(--(CALLS!A8:A500=DATE(2008,1)),--(CALLS!G8:G500="DP")

)
but I get the error too few arguments.

Gerald

 

[T. Valko]

One way:

=SUMPRODUCT(--(MONTH(CALLS!A8:A500)=1),--(CALLS!G8:G500="DP"))

Where 1 is the month number for January. Note that that applies to month 1
of *any* year. Also, that will evaluate empty cells as month 1.

Another option is to use cells to hold your date boundaries:

A1 = start date = 1/1/2008
B1 = end date = 1/31/2008

=SUMPRODUCT(--(CALLS!A8:A500>=A1),--(CALLS!A8:A500<=B1),--(CALLS!G8:G500="DP"))

 

[Rick Rothstein]

The DATE function requires 3 arguments (you left out the day argument); but,
depending on what is in CALLS!A8:A500, you may not need the DATE function.
For example, if that range contains month numbers, then you can compare them
to the number 1 (for you example)... if they contain real dates, then you
can apply the MONTH function to them and compare that to 1... and there are
other possibilities. If what I just posted doesn't help you, then you will
need to tell us what type of data is in CALLS!A8:A500.

 

[Mike H]

Try

=SUMPRODUCT(--(MONTH(Calls!A8:A500)=1),--(YEAR(Calls!A8:A500)=2008),--(Calls!G8:G500="DP"))

Mike

 

[gjameson via OfficeKB.com]

Outstanding Mike. Thank you, it works great.
Also thanks to the other that answered as well.

Gerald

Try

=SUMPRODUCT(--(MONTH(Calls!A8:A500)=1),--(YEAR(Calls!A8:A500)=2008),--(Calls!G8:G500="DP"))

Mike

I just want to count the occurrences for any given month in the year. How
would you set

[quoted text clipped - 6 lines]

Gerald

 

[Mike H]

Glad I could help

Outstanding Mike. Thank you, it works great.
Also thanks to the other that answered as well.

Gerald

Try

=SUMPRODUCT(--(MONTH(Calls!A8:A500)=1),--(YEAR(Calls!A8:A500)=2008),--(Calls!G8:G500="DP"))

Mike

I just want to count the occurrences for any given month in the year. How
would you set

[quoted text clipped - 6 lines]

Gerald