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.

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.

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.

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

Thanks Bob

I will try it

I guess the "*" is the answer!



"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 think so, it is a wildcard.

--
---
HTH

Bob

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



"Joan NYC" wrote in message
...
Thanks Bob

I will try it

I guess the "*" is the answer!



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


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

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.

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: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((A2:A20={"A","B","C","D"})*(B2:B20="ps ychiatrist"))

--
__________________________________
HTH

Bob

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

you saved my life, thanks for the post, my mgr will be pleased with the next
wave of spreadsheets

"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'm trying to do something similar. I need to count the number of apples in
column D. But i only need to count the ones in rows 25-35 and rows 50-75.
I've tried countif, sumif, and sumproduct. Is there a way to do this?

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

You could try this:

=COUNTIF(D25:D35,"apples")+COUNTIF(D50:D75,"apples ")

Hope this helps.

Pete

On Jan 13, 12:27*am, jolineachi
wrote:
I'm trying to do something similar. *I need to count the number of apples in
column D. *But i only need to count the ones in rows 25-35 and rows 50-75. *
I've tried countif, sumif, and sumproduct. *Is there a way to do this?



"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.- Hide quoted text -

- Show quoted text -

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.

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.

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

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

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.

Joan, I have the @ character in the same cell more than once and when I use
the =COUNTIF(A1:A100,"*@*") that Bob Phillips recommended, I get a count of
five instead of 10 in my test text. For example, ,
on five different rows in Excel should count 10 @ chars.

"Joan NYC" wrote:

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.

=countif() will count the number of cells that match the criteria.

If you want to count the number of @'s in a range:
=SUMPRODUCT(LEN(A1:A100)-LEN(SUBSTITUTE(A1:A100,"@","")))

Adjust the range to match, but you can't use the entire column until xl2007.



Paula Ohio wrote:

Joan, I have the @ character in the same cell more than once and when I use
the =COUNTIF(A1:A100,"*@*") that Bob Phillips recommended, I get a count of
five instead of 10 in my test text. For example, ,
on five different rows in Excel should count 10 @ chars.

"Joan NYC" wrote:

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.




--

Dave Peterson

eI am trying to put in the formula :
=COUNTIF(c4:c10, "cashiering")
to count recurrently rows of information.

The formula stays in the cell but the number does not materialize. I have
tried reformatting the cells to reflect number or general to no avail.

What am I doing wrong?

Cathy M

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

Format the cell as General.
With the cell still selected hit function key F2 then hit ENTER.

--
Biff
Microsoft Excel MVP


"Cathy M" <Cathy wrote in message
...
eI am trying to put in the formula :
=COUNTIF(c4:c10, "cashiering")
to count recurrently rows of information.

The formula stays in the cell but the number does not materialize. I have
tried reformatting the cells to reflect number or general to no avail.

What am I doing wrong?

Cathy M

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

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.

Hi,

Can anyone please help, i'm trying to count the total number of occupancies
"O" in column B are for "house" in column C - Dcounta and countif dont seem
to be working - any suggestions
thanks

Maybe this:

=SUMPRODUCT(--(B1:B10="O"),--(C1:C10="house"))

Note that with SUMPRODUCT you *can't* use entire columns as range references
unless you're using Excel 2007

--
Biff
Microsoft Excel MVP


"kEV" wrote in message
...
Hi,

Can anyone please help, i'm trying to count the total number of
occupancies
"O" in column B are for "house" in column C - Dcounta and countif dont
seem
to be working - any suggestions
thanks

thanks for your help!!

"T. Valko" wrote:

Maybe this:

=SUMPRODUCT(--(B1:B10="O"),--(C1:C10="house"))

Note that with SUMPRODUCT you *can't* use entire columns as range references
unless you're using Excel 2007

--
Biff
Microsoft Excel MVP


"kEV" wrote in message
...
Hi,

Can anyone please help, i'm trying to count the total number of
occupancies
"O" in column B are for "house" in column C - Dcounta and countif dont
seem
to be working - any suggestions
thanks

You're welcome!

--
Biff
Microsoft Excel MVP


"kEV" wrote in message
...
thanks for your help!!

"T. Valko" wrote:

Maybe this:

=SUMPRODUCT(--(B1:B10="O"),--(C1:C10="house"))

Note that with SUMPRODUCT you *can't* use entire columns as range
references
unless you're using Excel 2007

--
Biff
Microsoft Excel MVP


"kEV" wrote in message
...
Hi,

Can anyone please help, i'm trying to count the total number of
occupancies
"O" in column B are for "house" in column C - Dcounta and countif dont
seem
to be working - any suggestions
thanks

Hi I am trying to figure out how to count all the blocks that have text in
them. I have tried =COUNTIF(D2:D113,"*") but it counts only *, and it says in
the help section that it should work. does any one have any Idea's?

On Oct 30, 11:35*am, madchan001
wrote:
Hi I am trying to figure out how to count all the blocks that have text in
them. I have tried =COUNTIF(D2:D113,"*") but it counts only *, and it says in
the help section that it should work. does any one have any Idea's?

=SUMPRODUCT(--(ISTEXT(D2:D113)))

Hello,
I need to count how many times does a word "ITS" appear in my column. the
problem is that it appears multiple times within the same cell but excel
counts that cell just once instead of lets say 5 times when ITS appears 5
times in the cell. and also, my list is filtered so it looks like excel is
including the missing lines as well which i dont need.

thank you

One way, using a helper column.

1) In an unused column (say "H") enter

H1: =IF(SUBTOTAL(103,A1),A1)

and copy down as far as required, say, H1000. Hide the column.

2) In your target cell, array enter (CTRL-SHIFT-ENTER or CMD-RETURN):

=SUM(LEN(H1:H1000),-LEN(SUBSTITUTE(H1:H1000,"its","")))/LEN("its")


In article ,
Sedmikraska wrote:

Hello,
I need to count how many times does a word "ITS" appear in my column. the
problem is that it appears multiple times within the same cell but excel
counts that cell just once instead of lets say 5 times when ITS appears 5
times in the cell. and also, my list is filtered so it looks like excel is
including the missing lines as well which i dont need.

thank you

I have a similar problem I want to see how many of each text item are in a
column.
There only a dozen or so unique words out of a column of 450 rows but
that text is not static so I cant just use =COUNTIF(c4:c10, "text") as the
text value will change each month

Is there a way to get use the countif formula to read each cell text entry
and give me a total count of each text?

Thanks

Just to clarify. If I have a column that looks like this:
|Biscuits |
|Biscuits |
|Biscuits |
|Biscuits |
|Potatoes |
|Potatoes |
|Potatoes |
|Beef |
|Beef |
|Beef |
|Beef |
|Wine |

I want a result like this:
Wine = 1
Beef = 4
Potatoes = 3
Biscuits = 4

So that I dont have to type in Wine or beef in my formula. I need that
variable to be calculated and entered automatically. I know I can do it in
SQL but I dont want to have to create a database just for this....

Thanks


"Chgrec" wrote:

I have a similar problem I want to see how many of each text item are in a
column.
There only a dozen or so unique words out of a column of 450 rows but
that text is not static so I cant just use =COUNTIF(c4:c10, "text") as the
text value will change each month

Is there a way to get use the countif formula to read each cell text entry
and give me a total count of each text?

Thanks