I have a column with people who contacted us during a given month. Some
times the same person 2 to 3 time during the month. What funtion would I use
to count this list but only count duplicate names to = 1. Thank you.
1 Jim
2 stan
3 stan
4 bob
5 Jim
6 Scott
t = 4
Hi
you can use the following array function (enter it with control & shift &
enter, not just enter):
=SUM(1/COUNTIF(A1:A10,A1:A10))
where your data is in the range A1:A10
check out http://www.cpearson.com/excel/duplicat.htm for more options and
details
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"TBoe" <[email protected]> wrote in message
news:[email protected]...
>I have a column with people who contacted us during a given month. Some
> times the same person 2 to 3 time during the month. What funtion would I
> use
> to count this list but only count duplicate names to = 1. Thank you.
>
> 1 Jim
> 2 stan
> 3 stan
> 4 bob
> 5 Jim
> 6 Scott
>
> t = 4
Thanks much for your help Julie. Really appreceite it. I keep coming up
with 0 as the total. There's probably about 15 different names. I'll work
with that formula awile to see if it will solve this. I'm sure it's just me.
Thanks again.
Terry
"JulieD" wrote:
> Hi
>
> you can use the following array function (enter it with control & shift &
> enter, not just enter):
> =SUM(1/COUNTIF(A1:A10,A1:A10))
>
> where your data is in the range A1:A10
>
> check out http://www.cpearson.com/excel/duplicat.htm for more options and
> details
>
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ....well i'm working on it anyway
> "TBoe" <[email protected]> wrote in message
> news:[email protected]...
> >I have a column with people who contacted us during a given month. Some
> > times the same person 2 to 3 time during the month. What funtion would I
> > use
> > to count this list but only count duplicate names to = 1. Thank you.
> >
> > 1 Jim
> > 2 stan
> > 3 stan
> > 4 bob
> > 5 Jim
> > 6 Scott
> >
> > t = 4
>
>
>
Try the following...
=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))
....confirmed with ENTER only, or...
=SUM(IF(A1:A10<>"",1/COUNTIF(A1:A10,A1:A10)))
....confirmed with CONTROL+SHIFT+ENTER.
Hope this helps!
In article <[email protected]>,
"TBoe" <[email protected]> wrote:
> I have a column with people who contacted us during a given month. Some
> times the same person 2 to 3 time during the month. What funtion would I use
> to count this list but only count duplicate names to = 1. Thank you.
>
> 1 Jim
> 2 stan
> 3 stan
> 4 bob
> 5 Jim
> 6 Scott
>
> t = 4
Hi
are you sure you're using control & shift & enter to enter the formula and
not just enter
have you changed a1:a10 to your actual range?
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"TBoe" <[email protected]> wrote in message
news:[email protected]...
> Thanks much for your help Julie. Really appreceite it. I keep coming up
> with 0 as the total. There's probably about 15 different names. I'll
> work
> with that formula awile to see if it will solve this. I'm sure it's just
> me.
> Thanks again.
>
> Terry
>
> "JulieD" wrote:
>
>> Hi
>>
>> you can use the following array function (enter it with control & shift &
>> enter, not just enter):
>> =SUM(1/COUNTIF(A1:A10,A1:A10))
>>
>> where your data is in the range A1:A10
>>
>> check out http://www.cpearson.com/excel/duplicat.htm for more options and
>> details
>>
>> --
>> Cheers
>> JulieD
>> check out www.hcts.net.au/tipsandtricks.htm
>> ....well i'm working on it anyway
>> "TBoe" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have a column with people who contacted us during a given month. Some
>> > times the same person 2 to 3 time during the month. What funtion would
>> > I
>> > use
>> > to count this list but only count duplicate names to = 1. Thank you.
>> >
>> > 1 Jim
>> > 2 stan
>> > 3 stan
>> > 4 bob
>> > 5 Jim
>> > 6 Scott
>> >
>> > t = 4
>>
>>
>>
This is what I did Julie....
Copied and pasted the formula into the formula bar and changed the cell
coordinates
then hit control, shift, enter. Thanks again.
"JulieD" wrote:
> Hi
>
> are you sure you're using control & shift & enter to enter the formula and
> not just enter
> have you changed a1:a10 to your actual range?
>
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ....well i'm working on it anyway
> "TBoe" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks much for your help Julie. Really appreceite it. I keep coming up
> > with 0 as the total. There's probably about 15 different names. I'll
> > work
> > with that formula awile to see if it will solve this. I'm sure it's just
> > me.
> > Thanks again.
> >
> > Terry
> >
> > "JulieD" wrote:
> >
> >> Hi
> >>
> >> you can use the following array function (enter it with control & shift &
> >> enter, not just enter):
> >> =SUM(1/COUNTIF(A1:A10,A1:A10))
> >>
> >> where your data is in the range A1:A10
> >>
> >> check out http://www.cpearson.com/excel/duplicat.htm for more options and
> >> details
> >>
> >> --
> >> Cheers
> >> JulieD
> >> check out www.hcts.net.au/tipsandtricks.htm
> >> ....well i'm working on it anyway
> >> "TBoe" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I have a column with people who contacted us during a given month. Some
> >> > times the same person 2 to 3 time during the month. What funtion would
> >> > I
> >> > use
> >> > to count this list but only count duplicate names to = 1. Thank you.
> >> >
> >> > 1 Jim
> >> > 2 stan
> >> > 3 stan
> >> > 4 bob
> >> > 5 Jim
> >> > 6 Scott
> >> >
> >> > t = 4
> >>
> >>
> >>
>
>
>
Hi
i don't have any other ideas, do you want to email your workbook so i can
have a look? - send it direct to julied_ng at hcts dot net dot au - it's
11.38pm here so i probably won't get to it until tomorrow.
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"TBoe" <[email protected]> wrote in message
news:[email protected]...
> This is what I did Julie....
> Copied and pasted the formula into the formula bar and changed the cell
> coordinates
> then hit control, shift, enter. Thanks again.
>
> "JulieD" wrote:
>
>> Hi
>>
>> are you sure you're using control & shift & enter to enter the formula
>> and
>> not just enter
>> have you changed a1:a10 to your actual range?
>>
>> --
>> Cheers
>> JulieD
>> check out www.hcts.net.au/tipsandtricks.htm
>> ....well i'm working on it anyway
>> "TBoe" <[email protected]> wrote in message
>> news:[email protected]...
>> > Thanks much for your help Julie. Really appreceite it. I keep coming
>> > up
>> > with 0 as the total. There's probably about 15 different names. I'll
>> > work
>> > with that formula awile to see if it will solve this. I'm sure it's
>> > just
>> > me.
>> > Thanks again.
>> >
>> > Terry
>> >
>> > "JulieD" wrote:
>> >
>> >> Hi
>> >>
>> >> you can use the following array function (enter it with control &
>> >> shift &
>> >> enter, not just enter):
>> >> =SUM(1/COUNTIF(A1:A10,A1:A10))
>> >>
>> >> where your data is in the range A1:A10
>> >>
>> >> check out http://www.cpearson.com/excel/duplicat.htm for more options
>> >> and
>> >> details
>> >>
>> >> --
>> >> Cheers
>> >> JulieD
>> >> check out www.hcts.net.au/tipsandtricks.htm
>> >> ....well i'm working on it anyway
>> >> "TBoe" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >I have a column with people who contacted us during a given month.
>> >> >Some
>> >> > times the same person 2 to 3 time during the month. What funtion
>> >> > would
>> >> > I
>> >> > use
>> >> > to count this list but only count duplicate names to = 1. Thank
>> >> > you.
>> >> >
>> >> > 1 Jim
>> >> > 2 stan
>> >> > 3 stan
>> >> > 4 bob
>> >> > 5 Jim
>> >> > 6 Scott
>> >> >
>> >> > t = 4
>> >>
>> >>
>> >>
>>
>>
>>
Thanks much Domenic...that did it!
"Domenic" wrote:
> Try the following...
>
> =SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))
>
> ....confirmed with ENTER only, or...
>
> =SUM(IF(A1:A10<>"",1/COUNTIF(A1:A10,A1:A10)))
>
> ....confirmed with CONTROL+SHIFT+ENTER.
>
> Hope this helps!
>
> In article <[email protected]>,
> "TBoe" <[email protected]> wrote:
>
> > I have a column with people who contacted us during a given month. Some
> > times the same person 2 to 3 time during the month. What funtion would I use
> > to count this list but only count duplicate names to = 1. Thank you.
> >
> > 1 Jim
> > 2 stan
> > 3 stan
> > 4 bob
> > 5 Jim
> > 6 Scott
> >
> > t = 4
>
Thanks for you help and offer Julie. I guess it didn't like the copy/paste
method.
I entered it manually. Thanks again!
"JulieD" wrote:
> Hi
>
> i don't have any other ideas, do you want to email your workbook so i can
> have a look? - send it direct to julied_ng at hcts dot net dot au - it's
> 11.38pm here so i probably won't get to it until tomorrow.
>
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ....well i'm working on it anyway
> "TBoe" <[email protected]> wrote in message
> news:[email protected]...
> > This is what I did Julie....
> > Copied and pasted the formula into the formula bar and changed the cell
> > coordinates
> > then hit control, shift, enter. Thanks again.
> >
> > "JulieD" wrote:
> >
> >> Hi
> >>
> >> are you sure you're using control & shift & enter to enter the formula
> >> and
> >> not just enter
> >> have you changed a1:a10 to your actual range?
> >>
> >> --
> >> Cheers
> >> JulieD
> >> check out www.hcts.net.au/tipsandtricks.htm
> >> ....well i'm working on it anyway
> >> "TBoe" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Thanks much for your help Julie. Really appreceite it. I keep coming
> >> > up
> >> > with 0 as the total. There's probably about 15 different names. I'll
> >> > work
> >> > with that formula awile to see if it will solve this. I'm sure it's
> >> > just
> >> > me.
> >> > Thanks again.
> >> >
> >> > Terry
> >> >
> >> > "JulieD" wrote:
> >> >
> >> >> Hi
> >> >>
> >> >> you can use the following array function (enter it with control &
> >> >> shift &
> >> >> enter, not just enter):
> >> >> =SUM(1/COUNTIF(A1:A10,A1:A10))
> >> >>
> >> >> where your data is in the range A1:A10
> >> >>
> >> >> check out http://www.cpearson.com/excel/duplicat.htm for more options
> >> >> and
> >> >> details
> >> >>
> >> >> --
> >> >> Cheers
> >> >> JulieD
> >> >> check out www.hcts.net.au/tipsandtricks.htm
> >> >> ....well i'm working on it anyway
> >> >> "TBoe" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> >I have a column with people who contacted us during a given month.
> >> >> >Some
> >> >> > times the same person 2 to 3 time during the month. What funtion
> >> >> > would
> >> >> > I
> >> >> > use
> >> >> > to count this list but only count duplicate names to = 1. Thank
> >> >> > you.
> >> >> >
> >> >> > 1 Jim
> >> >> > 2 stan
> >> >> > 3 stan
> >> >> > 4 bob
> >> >> > 5 Jim
> >> >> > 6 Scott
> >> >> >
> >> >> > t = 4
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
Hi TBoe
glad it's solved
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"TBoe" <[email protected]> wrote in message
news:[email protected]...
> Thanks for you help and offer Julie. I guess it didn't like the
> copy/paste
> method.
> I entered it manually. Thanks again!
>
> "JulieD" wrote:
>
>> Hi
>>
>> i don't have any other ideas, do you want to email your workbook so i can
>> have a look? - send it direct to julied_ng at hcts dot net dot au - it's
>> 11.38pm here so i probably won't get to it until tomorrow.
>>
>> --
>> Cheers
>> JulieD
>> check out www.hcts.net.au/tipsandtricks.htm
>> ....well i'm working on it anyway
>> "TBoe" <[email protected]> wrote in message
>> news:[email protected]...
>> > This is what I did Julie....
>> > Copied and pasted the formula into the formula bar and changed the cell
>> > coordinates
>> > then hit control, shift, enter. Thanks again.
>> >
>> > "JulieD" wrote:
>> >
>> >> Hi
>> >>
>> >> are you sure you're using control & shift & enter to enter the formula
>> >> and
>> >> not just enter
>> >> have you changed a1:a10 to your actual range?
>> >>
>> >> --
>> >> Cheers
>> >> JulieD
>> >> check out www.hcts.net.au/tipsandtricks.htm
>> >> ....well i'm working on it anyway
>> >> "TBoe" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Thanks much for your help Julie. Really appreceite it. I keep
>> >> > coming
>> >> > up
>> >> > with 0 as the total. There's probably about 15 different names.
>> >> > I'll
>> >> > work
>> >> > with that formula awile to see if it will solve this. I'm sure it's
>> >> > just
>> >> > me.
>> >> > Thanks again.
>> >> >
>> >> > Terry
>> >> >
>> >> > "JulieD" wrote:
>> >> >
>> >> >> Hi
>> >> >>
>> >> >> you can use the following array function (enter it with control &
>> >> >> shift &
>> >> >> enter, not just enter):
>> >> >> =SUM(1/COUNTIF(A1:A10,A1:A10))
>> >> >>
>> >> >> where your data is in the range A1:A10
>> >> >>
>> >> >> check out http://www.cpearson.com/excel/duplicat.htm for more
>> >> >> options
>> >> >> and
>> >> >> details
>> >> >>
>> >> >> --
>> >> >> Cheers
>> >> >> JulieD
>> >> >> check out www.hcts.net.au/tipsandtricks.htm
>> >> >> ....well i'm working on it anyway
>> >> >> "TBoe" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> >I have a column with people who contacted us during a given month.
>> >> >> >Some
>> >> >> > times the same person 2 to 3 time during the month. What funtion
>> >> >> > would
>> >> >> > I
>> >> >> > use
>> >> >> > to count this list but only count duplicate names to = 1. Thank
>> >> >> > you.
>> >> >> >
>> >> >> > 1 Jim
>> >> >> > 2 stan
>> >> >> > 3 stan
>> >> >> > 4 bob
>> >> >> > 5 Jim
>> >> >> > 6 Scott
>> >> >> >
>> >> >> > t = 4
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>
very good forum
thank you so much
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks