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.

Hi Bob, I am hoping you can help me. I have a list of about 1000 employees
and column AC list their job role. i.e. Project Manager, Developer, etc. Is
there a way to get a count of how many people are in each role?

thanks,

robert

"Bob Phillips" wrote:

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.

You may want to use pivot charts. First you create a colum (to count) put a
value of 1 in that colum for all the 1000. Next, with your curser within your
worksheet, go to the Data and select the pivot tables. Follow the steps. It
will ask you whether to create the pivot in the same sheet or different
sheet. Choose different sheet. Next it will give you the option of organizing
the data the way you want. In the body, where it says data, put the count
variable which you generated. In the left hand colum put the job role. Pivot
table works beautifully, I just finished working on something similar to
yours. if this is not clear, go to the help menu and type in pivot tables,
they explain very well.

Hope it helps,
Nasreen

"Robert" wrote:

Hi Bob, I am hoping you can help me. I have a list of about 1000 employees
and column AC list their job role. i.e. Project Manager, Developer, etc. Is
there a way to get a count of how many people are in each role?

thanks,

robert

"Bob Phillips" wrote:

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.

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.

Does your formula look something like this:

=SUMPRODUCT((C1:C100="admin")*(D1:D100="sick"))

If you get a result of 0 then that means there are no matches. There may be
leading/traiking spaces or other unseen characters causing this. For
example:

_admin
admin_
_sick
sick_

Where the underscore represents an unseen character. Check your data and
make the necessary corrections.


--
Biff
Microsoft Excel MVP


"Steve Scatt" <Steve wrote in message
...
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.

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.

I am trying to use a count funtion for text.
=COUNTIF(K225:X225,"LOA")--this one works equal 1 but I need it to equal 3

But I need it to include to more labels can you help me.
=COUNTIF(K225:X225,"LOA,TRN,VAC")

How about a nice macro
'===========
Option Compare Text
Sub counttextinROW()
mr = 2
fc = Range("K1").Column
lc = Range("z1").Column

For i = fc To lc
If Cells(mr, i) = "loa" _
Or Cells(mr, i) = "b" _
Or Cells(mr, i) = "c" Then
mCount = mCount + 1
End If
Next i

MsgBox mCount
End Sub
'=============
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"LaTanya" wrote in message
...
I am trying to use a count funtion for text.
=COUNTIF(K225:X225,"LOA")--this one works equal 1 but I need it to equal 3

But I need it to include to more labels can you help me.
=COUNTIF(K225:X225,"LOA,TRN,VAC")

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.

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

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.

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

Hi all,

I'm looking to do something along the same lines but using text and numbers.
The formula below is what i've come up with

=SUMPRODUCT((A2:A20="reason")*(D2:D20,"<0""))

I get a value of 0 or an error returning. I'm also looking to do a range
for the numbers too, so B2 to B20 for numbers greater than 0 but less than 14
and equal to reason.

Hope you can help.

Many thanks

Andy



"Bob Phillips" wrote:

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

I think you need this:

=SUMPRODUCT((A2:A20="reason")*(D2:D20<""))

The second term means cells in D2:D20 are not blank.

Your second question can be achieved like this:

=SUMPRODUCT((A2:A20="reason")*(B2:B200)*(B2:B20<1 4))

Hope this helps.

Pete

On Jun 29, 1:41*pm, O2 andy <O2 wrote:
Hi all,

I'm looking to do something along the same lines but using text and numbers.
*The formula below is what i've come up with

=SUMPRODUCT((A2:A20="reason")*(D2:D20,"<0""))

I get a value of 0 or an error returning. *I'm also looking to do a range
for the numbers too, so B2 to B20 for numbers greater than 0 but less than 14
and equal to reason.

Hope you can help.

Many thanks

Andy



"Bob Phillips" wrote:
=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.- Hide quoted text -

- Show quoted text -

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 -

Thanks Pete! You're a life saver!!!

"Pete_UK" wrote:

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 -

You're welcome - thanks for feeding back.

Pete

On Jan 13, 2:15*am, jolineachi
wrote:
Thanks Pete! *You're a life saver!!!

I have another question:

I need to find the number of cases that were "Dismissed" in Column C between
"01/01/08" and "12/31/08" in Column D. The formula I tried was

=SUMPRODUCT(D149:D160="Guilty")*(E149:E160="01/01/07")*(E149:E160<="12/31/07")

I keep getting 0 when I should get 3. Can anyone help me?

"jolineachi" wrote:

Thanks Pete! You're a life saver!!!

"Pete_UK" wrote:

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