No, no trailing spaces. And now that I've entered the formula into a new
spreadsheet it's working as expected. Odd. There must have been something
strange with the data in the previous spreadsheet.

It's all working now, and I very much appreciate your help! Have a nice day.

Tricia

"Bob Phillips" wrote:

Tricia,

There is nothing wrong with the formula, I entered AA in A1, Completed in
F1, and I got a 1.

Does your AA or Completed have any trailing spaces?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Tricia S." wrote in message
...
It does fail, but I need to capture it in a count of all those that have
nothing in column B, but have a value in column F, among other criteria
(which I'm not having problems with <yet). The two below do not work (or
I'm doing something wrong)

I should get a result of 1 (but it's returning 0)


=SUMPRODUCT(--($B$1:$B$100=""),--($F$1:$F$100="Completed"),--($A$1:$A$100="A
A"))

=SUMPRODUCT(--($B$1:$B$100="
"),--($F$1:$F$100="Completed"),--($A$1:$A$100="AA"))

"Bob Phillips" wrote:

Tricia,

Surely if B is empty it fails the B1:B100=1 test. If you do want to
ignore
blanks, just add that as a condition

--(B1:B100<"")

Glad you like the SUMPRODUCT web page :-)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Tricia S." wrote in message
...
Thank you. A reply to another post lead me down the SUM(IF()) nested
functions which is working, but the SUMPRODUCT is much easier to read
and
maintain than nested SUM(IF()). I'd like to change over to the
SUMPRODUCT
function, however, I am unable to count where column B is empty (a
value
has
not yet been assigned to that row/column), but the other columns for
that
row
have values. I want to make sure not to count the blank rows at the
bottom
of the spreadsheet.

"Bob Phillips" wrote:

=SUMPRODUCT(--(B1:B100=1),--(F1:F100="Completed"))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Tricia S." <Tricia wrote in message
...
Iam using Excel 2003 and I am trying to count a row based on two
criteria.
For example: I am trying to count all those rows where cell B has
a
value
of
"1" AND cell F has a value of "Completed".
The COUNTIF() function appears to only accept one group of
parameters.
Using the COUNTIF() function twice in the cell with the + operator
between
them appears to add those that qualify for each criteria and the
"&"
appears
to be giving me results I have yet to figure out.

I am trying to build a summary table like below based on a
worksheet
with
many detail records (rows) in another worksheet in the same
workbook:
Status Level 1 Level 2 Level 3 .... Level 10
Open 10 13 1 24
Started 9 7 3 18
Completed 5 9 12 11
Etc