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?!!!

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?!!!

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?!!!

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?!!!