Bingo! David, you are a genius.

thanks so much for your expertise and patience.

Richard

"David Biddulph" wrote:

OK, now that you realise that to get the right answer you must ask the right
question, let's go back a few days.

Try changing
=SUMPRODUCT(('Q2'!D8:D33="chris craig")*('Q2'!E8:E33<"completed, carried
forward"))
to
=SUMPRODUCT(('Q2'!D8:D33="chris
craig")*('Q2'!E8:E33<"completed")*('Q2'!E8:E33<" carried forward"))
--
David Biddulph

Richard Horn wrote:
Oh my gosh! David I am so sorry.

The cell I am quering does not contain "completed, carried forward"

The cell would contain either "completed" or "carried forward". It
would be one or the otther.

I wanted to capture who the project lead is in column D, "chris
craig" and any project she is working on, unless the status is
"completed" or "carried forward" in column E. If the column E entry
is "completed" or "carried forward" I do not want that in the count.

Again, I apologize for not being so precise, as I know when making a
formula in Excel, you must be spot on.

richard

"David Biddulph" wrote:

As =LEN('Q2'!E8)=26 returned FALSE, that confirms (as I suggested
earlier) that the content of your cell is not "completed, carried
forward", because if you copy the string "completed, carried
forward" [without the quotes] into the cell and use the LEN function
on that you will see that it IS 26 characters.
What does =LEN('Q2'!E8) return? How many characters are in the
cell, if it isn't 26?
What does =CODE(RIGHT(A8)) return? It will be 100 if the last
character in the cell is "d". It will be 32 if the last character
is a space, and 160 if it is a non-breaking space.
--
David Biddulph

Richard Horn wrote:
Yes David, that's affirmative. I am certainily stuck.

I tried your 3 suggestions:
1. =('Q2'!E8<"completed, carried forward") which returned all TRUE
even though column does contain a Completed status.
2. =('Q2'!E8="completed, carried forward") which returned all FALSE
even though column does contain a Completed status.
3. =LEN('Q2'!E8)=26 which returned all FALSE even though column does
contain a Completed status.

Each of your suggested helpers I pasted in a cell, then copied them
down to cover a range of cells I wanted to test query.

Not unless I am totally misunderstanding what your trying to help me
with, I am not sure how to make this work, but I certainly
appreciate your patience is trying to share your experience.

thanks Richard

"David Biddulph" wrote:

So you've confirmed that you have a problem, and that your formula
doesn't match your data, but you've not bothered with my other
suggestions? --
David Biddulph

Richard Horn wrote:
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.