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.

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.

For instance:

Project Lead Status Date
Chris Craig Client Testing 05/15/09
Chris Craig Completed 04/17/09

What I want to return is "1". In other words, all projects that "chris
craig" is working on, that the project status is not "completed" or the
status is not "carried forward".

hope this helps.


"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.

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.

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.

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.

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.

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.

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.

"Joan NYC" wrote:

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.

I need to count the number of text characters per cell to ensure we don't go
over 34 characters a line for major print jobs. Each line will be in a
different cell. How can I do this?


"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.

Characters per cell =LEN(cellref)

All that does is tell you how many chars per cell including spaces.

Do you want to automatically limit the numbers of chars to a maximum of 34?

You could set up event code to truncate anything over a certain number of
characters after user hits ENTER key

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10" 'edit to suit
' "A1,A2,B1,C5,C6" for a non-contiguous range example
Dim cell As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Len(.Value) 34 Then
.Value = Left(.Value, 34)
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste into that module, Edit the range to suit. Alt + q to return to
the Excel window.


Gord Dibben MS Excel MVP

On Mon, 15 Jun 2009 08:24:05 -0700, LadyJags
wrote:

I need to count the number of text characters per cell to ensure we don't go
over 34 characters a line for major print jobs. Each line will be in a
different cell. How can I do this?


"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.

Hi,

Im hoping that someone can help me..
I am trying to count how many export has been done for each Region for July,
how many for August and so on.
Ill try to make myself a bit clear. For example:
My first column (A1:A100) contains 10 different Regions (Netherlands, Italy,
Spain etc). The second column (B1:B100) contains months ( July, August,
September, October etc). I would like to know if there is formula to count
how many times Netherlands July appears in those 2 columns.
In both columns data must be chosen from a drop down menu..
Hope I made myself clear.. Can this be done ?
Thanks
Antonella


"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.

Hi,

Im hoping that someone can help me..
I am trying to count how many export has been done for each Region for July,
how many for August and so on.
Ill try to make myself a bit clear. For example:
My first column (A1:A100) contains 10 different Regions (Netherlands, Italy,
Spain etc). The second column (B1:B100) contains months ( July, August,
September, October etc). I would like to know if there is formula to count
how many times Netherlands July appears in those 2 columns.
In both columns data must be chosen from a drop down menu..
Hope I made myself clear.. Can this be done ?
Thanks
Antonella


"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.

Hi,

Im hoping that you can help me..
I am trying to count how many export has been done for each Region for July,
how many for August and so on.
Ill try to make myself a bit clear. For example:
My first column (A1:A100) contains 10 different Regions (Netherlands, Italy,
Spain etc). The second column (B1:B100) contains months ( July, August,
September, October etc). I would like to know if there is formula to count
how many times Netherlands July appears in those 2 columns.
Hope I made myself clear.. Can this be done ?
I've tried COUNTIF but did not work. How can I nest COUNTIF and AND function?
Thanks


"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.

See the attached workbook.

There may be an easier way, but by using the DCOUNTA function, you can
define a critera range that can handle multiple criteria.

Hope this helps! :Bgr


+-------------------------------------------------------------------+
|Filename: Book2.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=181|
+-------------------------------------------------------------------+

--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117385

Sumproduct is the function that you'll want to use.

However, the proper configuration of the arguments in that function will
depend on *exactly* how the months are being entered into Column B.

Are the names of the months TEXT entries, OR, are they XL recognized dates,
formatted to display just the month name?

For months entered as text in Column B:
In C1 type in the name of the Region,
and in C2 type in the name of the month,
then try this:

=Sumproduct((A1:A100=C1)*(B1:B100=C2))

For months entered as "legal" XL dates in Column B,
try this:

=SUMPRODUCT((A1:A100=C1)*(TEXT(B1:B100,"mmmm")=C2) )

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Antonella" wrote in message
...
Hi,

Im hoping that someone can help me..
I am trying to count how many export has been done for each Region for
July,
how many for August and so on.
Ill try to make myself a bit clear. For example:
My first column (A1:A100) contains 10 different Regions (Netherlands,
Italy,
Spain etc). The second column (B1:B100) contains months ( July, August,
September, October etc). I would like to know if there is formula to count
how many times Netherlands July appears in those 2 columns.
In both columns data must be chosen from a drop down menu..
Hope I made myself clear.. Can this be done ?
Thanks
Antonella


"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.

A workbook illustrating a solution to your requirements was posted as a
reply to one of your previous posts with the same subject line.

Please check it out and, if it doesn't fit your needs, expand your
definition of the problem....


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117382

On Jul 19, 3:43 pm, jamescox wrote:
A workbook illustrating a solution to your requirements was posted as a
reply to one of your previous posts with the same subject line.

Please check it out and, if it doesn't fit your needs, expand your
definition of the problem....

--
jamescox
------------------------------------------------------------------------
jamescox's Profile:http://www.thecodecage.com/forumz/member.php?userid=449
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=117382

Hi,
for an alternative to SUMPRODUCT have a look at http://sulprobil.com/html/listfreq.html,
maybe you find help there.

Have fun, cheers
Michael

I agree!!! Bob...you are a WONDERFUL man! I too could not figure out why it
was not working until I added the *'s in. Worked like a charm! :) God
bless!


"catwoman48" wrote:

"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.


<<SMAK
You wonderful person! I just spent an hour and a half doing research on
this very thing. Couldn't figure out why my formula(s) wouldn't work. I had
"wildcard" in the back of my mind, but that seemed too simple, and didn't
make sense, as my search criterion was not a symbol. But it worked, and I
thank you a million times.

David
I have a similar problem where I am tring to count the occurance of a text
in one column and a number value in a second column. For instance conlum A
contains 'Y' or 'N' and column C contians numbers 1, 2, 3, etc. So I want to
count the number of occurances where column A is 'Y' and column C is '3'.
Can you help with this?

Thanks
conner34

"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.

=sumproduct(--(a1:a1000="Y"),--(c1:c1000=3))
Adjust the ranges to suit.

Regards,
Fred

"conner34" wrote in message
...
David
I have a similar problem where I am tring to count the occurance of a text
in one column and a number value in a second column. For instance conlum
A
contains 'Y' or 'N' and column C contians numbers 1, 2, 3, etc. So I want
to
count the number of occurances where column A is 'Y' and column C is '3'.
Can you help with this?

Thanks
conner34

"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.

"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.

Hi - I'm using the Count If formula you mentioned below...My question is - if
you are summing text and you want the formula to be maintained if you add
columns in later, how do you do that? For example, if I am counting the
number of x's in columns A1:A10 and I add a column at A5, I want to be sure
it automatically includes those in the sum and that the formula now covers
A1:A11....

"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.

The formula should automatically adjust for the inserted ROW.

=COUNTIF(A1:A10,"x")

If I insert a new ROW 5 the formula automatically adjusts to:

=COUNTIF(A1:A11,"x")

--
Biff
Microsoft Excel MVP


"Debbie Amateur" wrote in message
...
Hi - I'm using the Count If formula you mentioned below...My question is -
if
you are summing text and you want the formula to be maintained if you add
columns in later, how do you do that? For example, if I am counting the
number of x's in columns A1:A10 and I add a column at A5, I want to be
sure
it automatically includes those in the sum and that the formula now covers
A1:A11....

"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.

I just tried this and it worked perfectly! Thank you!

"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.

I have another instance that I need help with.

I have a string of letters in 1 cell and I want to count how many times a
letter occurs in that cell and eventually count all occurances of all letters
in that one cell. Any ideas?

As an example:

A1 contains "catgctagccatgca" as text. I want to know many times a, c, g,
and t occurs in that string.

"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.

Hello Bob,

The below function helped me start out. But I need to add the letters in
each column and give them different values and then subsequently add them for
a total of nurses scheduled. How would I go about creating that function?
Thank you so much for any help you can give.

"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.