Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compound Criteria for CountIf
Excel 2003
I have a worksheet with the following data in cells B7:C12. Dept. Date DOC 11/13/2008 DOT 12/1/2008 AGR 12/12/2008 AGR 12/23/2008 AGR 1/10/2009 DOJ 2/10/2009 I need a way to count all the occurrences for a given department name that fall into a given date range. For example, I need to count all the entries for "AGR" in the month of December. Using COUNTIF() I can count all occurrences of "AGR", but I can't figure out how to get just the ones in the month of December. Currently, the file is very small, but it may well grow to be thousands of lines, so I need a solution that will perform well for a large amount of data. Any help that you can offer will be greatly appreciated. --Tom |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compound Criteria for CountIf
Try this:
=SUMPRODUCT((B7:B2000="AGR")*(TEXT(C7:C2000,"MMM-YY")="Dec-08")) Adjust the ranges to suit, but you can't use full-column references in Excel 2003. Hope this helps. Pete On Nov 25, 1:31*am, "Thomas M." wrote: Excel 2003 I have a worksheet with the following data in cells B7:C12. Dept. * Date DOC * 11/13/2008 DOT * 12/1/2008 AGR * 12/12/2008 AGR * 12/23/2008 AGR * 1/10/2009 DOJ * *2/10/2009 I need a way to count all the occurrences for a given department name that fall into a given date range. *For example, I need to count all the entries for "AGR" in the month of December. *Using COUNTIF() I can count all occurrences of "AGR", but I can't figure out how to get just the ones in the month of December. Currently, the file is very small, but it may well grow to be thousands of lines, so I need a solution that will perform well for a large amount of data. Any help that you can offer will be greatly appreciated. --Tom |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compound Criteria for CountIf
Hi,
You can do this with a formula or a pivot table. Here is a formula approach: =SUMPRODUCT(--(B7:B12="AGR"),--(MONTH(C7:C12)=12)) If you enter the type, AGR in cell A1 and the month number 12, in A2 the formula would be =SUMPRODUCT(--(B7:B12=A1),--(MONTH(C7:C12)=A2)) If this helps, please click the Yes button Cheers, Shane Devenshire "Thomas M." wrote: Excel 2003 I have a worksheet with the following data in cells B7:C12. Dept. Date DOC 11/13/2008 DOT 12/1/2008 AGR 12/12/2008 AGR 12/23/2008 AGR 1/10/2009 DOJ 2/10/2009 I need a way to count all the occurrences for a given department name that fall into a given date range. For example, I need to count all the entries for "AGR" in the month of December. Using COUNTIF() I can count all occurrences of "AGR", but I can't figure out how to get just the ones in the month of December. Currently, the file is very small, but it may well grow to be thousands of lines, so I need a solution that will perform well for a large amount of data. Any help that you can offer will be greatly appreciated. --Tom |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compound Criteria for CountIf
Worked perfectly. Thanks!
--Tom "Pete_UK" wrote in message ... Try this: =SUMPRODUCT((B7:B2000="AGR")*(TEXT(C7:C2000,"MMM-YY")="Dec-08")) Adjust the ranges to suit, but you can't use full-column references in Excel 2003. Hope this helps. Pete On Nov 25, 1:31 am, "Thomas M." wrote: Excel 2003 I have a worksheet with the following data in cells B7:C12. Dept. Date DOC 11/13/2008 DOT 12/1/2008 AGR 12/12/2008 AGR 12/23/2008 AGR 1/10/2009 DOJ 2/10/2009 I need a way to count all the occurrences for a given department name that fall into a given date range. For example, I need to count all the entries for "AGR" in the month of December. Using COUNTIF() I can count all occurrences of "AGR", but I can't figure out how to get just the ones in the month of December. Currently, the file is very small, but it may well grow to be thousands of lines, so I need a solution that will perform well for a large amount of data. Any help that you can offer will be greatly appreciated. --Tom |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compound Criteria for CountIf
Thanks. I haven't had a chance to try the pivot table suggestion yet, but I
will in the next few days because I need to learn how to do pivot tables anyway. --Tom "Shane Devenshire" wrote in message ... Hi, You can do this with a formula or a pivot table. Here is a formula approach: =SUMPRODUCT(--(B7:B12="AGR"),--(MONTH(C7:C12)=12)) If you enter the type, AGR in cell A1 and the month number 12, in A2 the formula would be =SUMPRODUCT(--(B7:B12=A1),--(MONTH(C7:C12)=A2)) If this helps, please click the Yes button Cheers, Shane Devenshire "Thomas M." wrote: Excel 2003 I have a worksheet with the following data in cells B7:C12. Dept. Date DOC 11/13/2008 DOT 12/1/2008 AGR 12/12/2008 AGR 12/23/2008 AGR 1/10/2009 DOJ 2/10/2009 I need a way to count all the occurrences for a given department name that fall into a given date range. For example, I need to count all the entries for "AGR" in the month of December. Using COUNTIF() I can count all occurrences of "AGR", but I can't figure out how to get just the ones in the month of December. Currently, the file is very small, but it may well grow to be thousands of lines, so I need a solution that will perform well for a large amount of data. Any help that you can offer will be greatly appreciated. --Tom |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compound Criteria for CountIf
You're welcome, Tom - thanks for feeding back.
Pete On Nov 26, 9:56*pm, "Thomas M." wrote: Worked perfectly. *Thanks! --Tom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compound CountIf formula | Excel Worksheet Functions | |||
Compound condition with COUNTIF | Excel Discussion (Misc queries) | |||
Countif using format criteria not number criteria? | Excel Worksheet Functions | |||
Countif using format criteria....not number criteria? | Excel Worksheet Functions | |||
COUNTIF with compound comparison | Excel Worksheet Functions |