Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Sumproduct
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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200812/1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Sumproduct
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")) -- Biff Microsoft Excel MVP "gjameson via OfficeKB.com" <u21717@uwe wrote in message news:8ee86e2332540@uwe... 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200812/1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Sumproduct
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. -- Rick (MVP - Excel) "gjameson via OfficeKB.com" <u21717@uwe wrote in message news:8ee86e2332540@uwe... 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200812/1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Sumproduct
Try
=SUMPRODUCT(--(MONTH(Calls!A8:A500)=1),--(YEAR(Calls!A8:A500)=2008),--(Calls!G8:G500="DP")) Mike "gjameson via OfficeKB.com" wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200812/1 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Sumproduct
Outstanding Mike. Thank you, it works great.
Also thanks to the other that answered as well. Gerald Mike H wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200812/1 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif Sumproduct
Glad I could help
"gjameson via OfficeKB.com" wrote: Outstanding Mike. Thank you, it works great. Also thanks to the other that answered as well. Gerald Mike H wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200812/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif - Sumproduct | Excel Worksheet Functions | |||
COUNTIF or SUMPRODUCT | Excel Discussion (Misc queries) | |||
SumProduct and Countif | Excel Worksheet Functions | |||
Sumproduct or Countif | Excel Worksheet Functions | |||
SUMPRODUCT & COUNTIF | Excel Worksheet Functions |