Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 29
Default COUNTIF based on several criteria incl. a "does not equal" criteri

I am trying to create a formula that will count the total number of cells
that meet multiple criteria, including a "does not equal" criteria.
Basically, I am trying to count the total number of cells that meet the
following:

Column O = "ROH"
Column H is "Not" blank, and
Column J = 1/5/2008

I have tried the following SumProduct formula, but it is not yielding the
correct result:

=SUMPRODUCT((H2:H350<" "),(J2:J350="1/5/2008"),(O2:O350="ROH"))

For whatever reason, I am getting a "0" result when I know there should be
at least 7. What am I doing wrong?!!!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default COUNTIF based on several criteria incl. a "does not equal" criteri

Try it like this:

=SUMPRODUCT(--(H2:H350<""),--(J2:J350=DATE(2008,1,5)),--(O2:O350="ROH"))

Or, use cells to hold the date and text criteria:

A1 = 1/5/2008
B1 = ROH

=SUMPRODUCT(--(H2:H350<""),--(J2:J350=A1),--(O2:O350=B1))


--
Biff
Microsoft Excel MVP


"MsBeverlee" wrote in message
...
I am trying to create a formula that will count the total number of cells
that meet multiple criteria, including a "does not equal" criteria.
Basically, I am trying to count the total number of cells that meet the
following:

Column O = "ROH"
Column H is "Not" blank, and
Column J = 1/5/2008

I have tried the following SumProduct formula, but it is not yielding the
correct result:

=SUMPRODUCT((H2:H350<" "),(J2:J350="1/5/2008"),(O2:O350="ROH"))

For whatever reason, I am getting a "0" result when I know there should be
at least 7. What am I doing wrong?!!!



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 91
Default COUNTIF based on several criteria incl. a "does not equal" cri

I'm learning how to make formulas and have a question. What does the -- do
in the front of each variable?



"T. Valko" wrote:

Try it like this:

=SUMPRODUCT(--(H2:H350<""),--(J2:J350=DATE(2008,1,5)),--(O2:O350="ROH"))

Or, use cells to hold the date and text criteria:

A1 = 1/5/2008
B1 = ROH

=SUMPRODUCT(--(H2:H350<""),--(J2:J350=A1),--(O2:O350=B1))


--
Biff
Microsoft Excel MVP


"MsBeverlee" wrote in message
...
I am trying to create a formula that will count the total number of cells
that meet multiple criteria, including a "does not equal" criteria.
Basically, I am trying to count the total number of cells that meet the
following:

Column O = "ROH"
Column H is "Not" blank, and
Column J = 1/5/2008

I have tried the following SumProduct formula, but it is not yielding the
correct result:

=SUMPRODUCT((H2:H350<" "),(J2:J350="1/5/2008"),(O2:O350="ROH"))

For whatever reason, I am getting a "0" result when I know there should be
at least 7. What am I doing wrong?!!!




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default COUNTIF based on several criteria incl. a "does not equal" cri

The double unary minus coerces the boolean TRUE or FALSE to a number 1 or 0.
The first minus converts TRUE to -1, and the second makes it +1.
--
David Biddulph

"JICDB" wrote in message
...
I'm learning how to make formulas and have a question. What does the --
do
in the front of each variable?



"T. Valko" wrote:

Try it like this:

=SUMPRODUCT(--(H2:H350<""),--(J2:J350=DATE(2008,1,5)),--(O2:O350="ROH"))

Or, use cells to hold the date and text criteria:

A1 = 1/5/2008
B1 = ROH

=SUMPRODUCT(--(H2:H350<""),--(J2:J350=A1),--(O2:O350=B1))


--
Biff
Microsoft Excel MVP


"MsBeverlee" wrote in message
...
I am trying to create a formula that will count the total number of
cells
that meet multiple criteria, including a "does not equal" criteria.
Basically, I am trying to count the total number of cells that meet the
following:

Column O = "ROH"
Column H is "Not" blank, and
Column J = 1/5/2008

I have tried the following SumProduct formula, but it is not yielding
the
correct result:

=SUMPRODUCT((H2:H350<" "),(J2:J350="1/5/2008"),(O2:O350="ROH"))

For whatever reason, I am getting a "0" result when I know there should
be
at least 7. What am I doing wrong?!!!






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
Macro to concatenate into "B1" B2 thru B"x" based on new data in "Col A" Dennis Excel Discussion (Misc queries) 0 July 17th 06 02:38 PM
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next BCB New Users to Excel 7 May 13th 06 10:02 PM
sort data rows "greater than or equal" criteria in another cell HV man Excel Worksheet Functions 0 March 26th 06 11:50 PM
Using "COUNTIF" with more than 1 criteria TISSMOD Excel Worksheet Functions 12 January 26th 06 09:48 PM
"IF"- "THEN" type Formula based on Null value Jay Excel Worksheet Functions 8 November 17th 05 10:05 AM


All times are GMT +1. The time now is 07:49 PM.

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"