Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 470
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 470
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 470
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Nesting COUNTIF for multiple criteria in multiple columns NeedExcelHelp07 Excel Worksheet Functions 1 December 12th 07 06:47 PM
Using Countif with multiple criteria bj Excel Worksheet Functions 0 August 3rd 06 07:16 PM
Multiple Criteria using countif calmo Excel Worksheet Functions 6 October 25th 05 08:33 PM
Countif for multiple criteria Jim Rennie Excel Worksheet Functions 3 June 21st 05 01:38 AM
Countif with multiple criteria and multiple worksheets JJ Excel Worksheet Functions 1 December 28th 04 07:37 PM


All times are GMT +1. The time now is 03:13 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"