With the helper column I copied down, it did not return as it should:

Whether I used =('Q2'!E8<"completed, carried forward") which returned all
TRUE even though column does contain a Completed status.

or

=('Q2'!E8="completed, carried forward") which returned all FALSE even though
column does contain a Completed status.

For column E on my Q2 worksheet I am using a Validation, List, from the
bottom of my sheet, so the project lead can have a drop-down box to select
their current status. So there would not be any abnormal characters or spaces
when I am trying to comprise my formula.

"David Biddulph" wrote:

That probably means that you've got some slight difference in the content of
column E, perhaps spare spaces or other characters (such as non-breaking
spaces) in the cell.

You can check with a helper column:
=('Q2'!E8<"completed, carried forward") and copy down
and/or
=('Q2'!E8="completed, carried forward") and copy down.

You might also want to try =LEN('Q2'!E8)=26

You might wish to copy the string (which you think is ="completed, carried
forward") from a suitable cell in column E into your formula.
--
David Biddulph

"Richard Horn" <Richard wrote in message
...
I found these comments very helpful so far. Now I am stumped.

I have a workbook with multiple worksheets and I am making a summary page
up
front for management review.

Here's what I am trying to do. From worksheet named Q2, I have all my
projects leads in column D, and then the current status of their projects
in
column E, for instance, intake, editing, pending, etc.. What I am trying
to
do is count all the instances of one project lead, in this case chris
craig,
and then I want to know all projects she is working on, unless, or except,
if
the status is completed or carried forward. If the status for a project
lead
is completed or carried, then do not count.

This is the formula I am trying to use, but is is counting all projects
for
the given lead with all statuses, including completed and carried forward.

=SUMPRODUCT(('Q2'!D8:D33="chris craig")*('Q2'!E8:E33<"completed, carried
forward"))

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Steve Scatt" wrote in message
...
Thanks Biff,

tried it again and made sure no unseen characters and it seems to work.
Cheers.

"Steve Scatt" wrote:

I have 2 separate columns and I am trying to count the nu,ber of
occurances
for a 2 different values. i.e. how many times admin column C and sick
Column
D show up. Tried the SUMPRODUCT and it doesn't seem to work. I get a
value of
0 when there should be 3.

"David Biddulph" wrote:

=SUMPRODUCT((A1:A100="A")*(A1:A100<="D")*(B1:B100 ="psychiatrist"))
--
David Biddulph

"Nick Brunetti" wrote in
message
...
I am trying to count a cell if criteria from two separate columns
are
met.

For example, my first column has the letters A, B, C, D or no
letters
at
all. The second column has different descriptive words (i.e.
internal
medicine, psychiatrist, etc). I would like a cell to be counted
if a
cell
in
the first column contains an A or B or C or D AND a cell in the
second
colum
contains the word "psychiatrist".

What is the best function/formula to use for this problem?

"Bob Phillips" wrote:

=COUNTIF(A1:A100,"*@*")

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail
in
my
addy)



"Joan NYC" wrote in message
...
Aha... Sumproduct is not a function I ever tried

I have a column of text. Some cells contain the character "@"
with a
space
preceding the rest of text in the cell. I realize the @ could
be
considered
an operator so it is preceded with an apostrophe to designate
text. I
want
to sum the cells that have the "@"

Thanks

"David Biddulph" wrote:

You'll need to be a bit clearer as to what you're trying to
do.
If
COUNTIF
or SUMIF doesn't do the job, you may want to try SUMPRODUCT.
--
David Biddulph

"Joan NYC" wrote in
message
...
I have been trying to calculate a column of Text in order to
sum
contents
by
certain criteria

I have tried Count, CoutA and CountIF and cannot be able to
get
anything
to
work.

Am I barking up the wrong tree? :) Can this be done?

Thanks much.