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
:
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.
:
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
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
33="chris
craig")*('Q2'!E8:E33<>"completed, carried forward"))
:
You're welcome. Thanks for the feedback!
--
Biff
Microsoft Excel MVP
message
Thanks Biff,
tried it again and made sure no unseen characters and it seems
to work. Cheers.
:
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.
:
=SUMPRODUCT((A1:A100>="A")*(A1:A100<="D")*(B1:B100="psychiatrist"))
--
David Biddulph
"Nick Brunetti" <
[email protected]>
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?
:
=COUNTIF(A1:A100,"*@*")
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be
gmail in
my
addy)
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
:
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
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.
