|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
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" <JoanNYC@discussions.microsoft.com> wrote in message
> news:1E58761B-5B4B-40FC-BD31-7459F9AF9CFF@microsoft.com...
> > 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" <JoanNYC@discussions.microsoft.com> wrote in message
> >> news:75DDC879-6816-47A9-A405-433C2B26D33A@microsoft.com...
> >> > 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" <JoanNYC@discussions.microsoft.com> wrote in message
> >> >> news:54D51227-AB8B-42DC-B3C8-64DCD126287B@microsoft.com...
> >> >> >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.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
> >>
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
|
|
| |
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" <JoanNYC@discussions.microsoft.com> wrote in message
> > news:1E58761B-5B4B-40FC-BD31-7459F9AF9CFF@microsoft.com...
> > > 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" <JoanNYC@discussions.microsoft.com> wrote in message
> > >> news:75DDC879-6816-47A9-A405-433C2B26D33A@microsoft.com...
> > >> > 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" <JoanNYC@discussions.microsoft.com> wrote in message
> > >> >> news:54D51227-AB8B-42DC-B3C8-64DCD126287B@microsoft.com...
> > >> >> >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.
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> > >>
> >
> >
> > |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
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" <JoanNYC@discussions.microsoft.com> wrote in message
> news:75DDC879-6816-47A9-A405-433C2B26D33A@microsoft.com...
> > 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" <JoanNYC@discussions.microsoft.com> wrote in message
> >> news:54D51227-AB8B-42DC-B3C8-64DCD126287B@microsoft.com...
> >> >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.
> >>
> >>
> >>
>
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
=SUMPRODUCT((A1:A100>="A")*(A1:A100<="D")*(B1:B100="psychiatrist"))
--
David Biddulph
"Nick Brunetti" <NickBrunetti@discussions.microsoft.com> wrote in message
news:84BD7EE0-E3B9-48C3-A56A-5E1DE06B4D28@microsoft.com...
>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" <JoanNYC@discussions.microsoft.com> wrote in message
>> news:75DDC879-6816-47A9-A405-433C2B26D33A@microsoft.com...
>> > 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" <JoanNYC@discussions.microsoft.com> wrote in message
>> >> news:54D51227-AB8B-42DC-B3C8-64DCD126287B@microsoft.com...
>> >> >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.
>> >>
>> >>
>> >>
>>
>>
>>
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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" <NickBrunetti@discussions.microsoft.com> wrote in message
> news:84BD7EE0-E3B9-48C3-A56A-5E1DE06B4D28@microsoft.com...
> >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" <JoanNYC@discussions.microsoft.com> wrote in message
> >> news:75DDC879-6816-47A9-A405-433C2B26D33A@microsoft.com...
> >> > 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" <JoanNYC@discussions.microsoft.com> wrote in message
> >> >> news:54D51227-AB8B-42DC-B3C8-64DCD126287B@microsoft.com...
> >> >> >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.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
> >>
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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 Scatt@discussions.microsoft.com> wrote in message
news:EE03904D-F8FE-42A9-9456-D75E3FF5C171@microsoft.com...
>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" <NickBrunetti@discussions.microsoft.com> wrote in message
>> news:84BD7EE0-E3B9-48C3-A56A-5E1DE06B4D28@microsoft.com...
>> >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" <JoanNYC@discussions.microsoft.com> wrote in message
>> >> news:75DDC879-6816-47A9-A405-433C2B26D33A@microsoft.com...
>> >> > 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" <JoanNYC@discussions.microsoft.com> wrote in message
>> >> >> news:54D51227-AB8B-42DC-B3C8-64DCD126287B@microsoft.com...
>> >> >> >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.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>> >>
>>
>>
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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" <NickBrunetti@discussions.microsoft.com> wrote in message
> > news:84BD7EE0-E3B9-48C3-A56A-5E1DE06B4D28@microsoft.com...
> > >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" <JoanNYC@discussions.microsoft.com> wrote in message
> > >> news:75DDC879-6816-47A9-A405-433C2B26D33A@microsoft.com...
> > >> > 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" <JoanNYC@discussions.microsoft.com> wrote in message
> > >> >> news:54D51227-AB8B-42DC-B3C8-64DCD126287B@microsoft.com...
> > >> >> >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.
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> > >>
> >
> >
> > |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
Question
Answer
Notify me of replies