Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF with multiple criteria
I don't think this can be done with COUNTIF, but I need to count using two
criterias: The two criterias a 1) A8:A1000 1/1/08 2) G8:G1000 < "" Thanks for your help. Les |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF with multiple criteria
=sumproduct(--(a1:a1000date(2008,1,1)),--(g8:g1000<""))
Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html ====== xl2007 has a couple of new functions: =countifs() and =sumifs() WLMPilot wrote: I don't think this can be done with COUNTIF, but I need to count using two criterias: The two criterias a 1) A8:A1000 1/1/08 2) G8:G1000 < "" Thanks for your help. Les -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF with multiple criteria
Nevermind, I found the error. A1 needed to be A8.
Thanks, Les "Dave Peterson" wrote: =sumproduct(--(a1:a1000date(2008,1,1)),--(g8:g1000<"")) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html ====== xl2007 has a couple of new functions: =countifs() and =sumifs() WLMPilot wrote: I don't think this can be done with COUNTIF, but I need to count using two criterias: The two criterias a 1) A8:A1000 1/1/08 2) G8:G1000 < "" Thanks for your help. Les -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF with multiple criteria
The formula you provided is in C5 which is formated to numbers (no decimal)
and I got a #Value error. Les "Dave Peterson" wrote: =sumproduct(--(a1:a1000date(2008,1,1)),--(g8:g1000<"")) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html ====== xl2007 has a couple of new functions: =countifs() and =sumifs() WLMPilot wrote: I don't think this can be done with COUNTIF, but I need to count using two criterias: The two criterias a 1) A8:A1000 1/1/08 2) G8:G1000 < "" Thanks for your help. Les -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF with multiple criteria
Sorry about the typo!
WLMPilot wrote: Nevermind, I found the error. A1 needed to be A8. Thanks, Les "Dave Peterson" wrote: =sumproduct(--(a1:a1000date(2008,1,1)),--(g8:g1000<"")) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html ====== xl2007 has a couple of new functions: =countifs() and =sumifs() WLMPilot wrote: I don't think this can be done with COUNTIF, but I need to count using two criterias: The two criterias a 1) A8:A1000 1/1/08 2) G8:G1000 < "" Thanks for your help. Les -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF with multiple criteria
Hi,
Please type 1/1/2008 in cell B1. Now use the following formula =sumproduct((A8:A1000B1)*(G8:G1000<"")) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "WLMPilot" wrote in message ... I don't think this can be done with COUNTIF, but I need to count using two criterias: The two criterias a 1) A8:A1000 1/1/08 2) G8:G1000 < "" Thanks for your help. Les |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting COUNTIF for multiple criteria in multiple columns | Excel Worksheet Functions | |||
Using Countif with multiple criteria | Excel Worksheet Functions | |||
Multiple Criteria using countif | Excel Worksheet Functions | |||
Countif for multiple criteria | Excel Worksheet Functions | |||
Countif with multiple criteria and multiple worksheets | Excel Worksheet Functions |