COUNTIF question
 

With =COUNTIF(A1:A700,A1) in cell B1 and copied down, entries which have a
space after the final letter are, quite correctly, treated as a different
word and therefore not included in the total for a particular word. IE:

[testing] and [testing ] are treated as different words. (brackets to show
the space)
How can I adapt the formula to ignore any space after the final letter in
the cell?

Thanks,

--
Traa Dy Liooar

Jock

COUNTIF question
 

=COUNTIF($A$1:$A$700,TRIM(A1) )

Regards,
Stefi


€žJock€� ezt Ã*rta:

With =COUNTIF(A1:A700,A1) in cell B1 and copied down, entries which have a
space after the final letter are, quite correctly, treated as a different
word and therefore not included in the total for a particular word. IE:

[testing] and [testing ] are treated as different words. (brackets to show
the space)
How can I adapt the formula to ignore any space after the final letter in
the cell?

Thanks,

--
Traa Dy Liooar

Jock

COUNTIF question
 

One way:

=sumproduct(--(trim(A1:A700)=trim(A1)))

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Jock wrote:

With =COUNTIF(A1:A700,A1) in cell B1 and copied down, entries which have a
space after the final letter are, quite correctly, treated as a different
word and therefore not included in the total for a particular word. IE:

[testing] and [testing ] are treated as different words. (brackets to show
the space)
How can I adapt the formula to ignore any space after the final letter in
the cell?

Thanks,

--
Traa Dy Liooar

Jock

--

Dave Peterson

COUNTIF question
 

I missed a note:

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html



Jock wrote:

With =COUNTIF(A1:A700,A1) in cell B1 and copied down, entries which have a
space after the final letter are, quite correctly, treated as a different
word and therefore not included in the total for a particular word. IE:

[testing] and [testing ] are treated as different words. (brackets to show
the space)
How can I adapt the formula to ignore any space after the final letter in
the cell?

Thanks,

--
Traa Dy Liooar

Jock

--

Dave Peterson

COUNTIF question
 

Thanks but I can't get it to work. I don't think TRIM works within a COUNTIF
formula.
--
Traa Dy Liooar

Jock


"Stefi" wrote:

=COUNTIF($A$1:$A$700,TRIM(A1) )

Regards,
Stefi


€žJock€� ezt Ã*rta:

With =COUNTIF(A1:A700,A1) in cell B1 and copied down, entries which have a
space after the final letter are, quite correctly, treated as a different
word and therefore not included in the total for a particular word. IE:

[testing] and [testing ] are treated as different words. (brackets to show
the space)
How can I adapt the formula to ignore any space after the final letter in
the cell?

Thanks,

--
Traa Dy Liooar

Jock

COUNTIF question
 

Nice one Dave, thanks for that.
--
Traa Dy Liooar

Jock


"Dave Peterson" wrote:

One way:

=sumproduct(--(trim(A1:A700)=trim(A1)))

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Jock wrote:

With =COUNTIF(A1:A700,A1) in cell B1 and copied down, entries which have a
space after the final letter are, quite correctly, treated as a different
word and therefore not included in the total for a particular word. IE:

[testing] and [testing ] are treated as different words. (brackets to show
the space)
How can I adapt the formula to ignore any space after the final letter in
the cell?

Thanks,

--
Traa Dy Liooar

Jock

--

Dave Peterson

COUNTIF question
 

Thinking further, if a name were to appear, say six times, would it be
possible to only show the result aagainst the first entry rather than all
six? (The names are listed by fastest times in ascending order, not
alphabetically)
--
Traa Dy Liooar

Jock


"Dave Peterson" wrote:

I missed a note:

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html



Jock wrote:

With =COUNTIF(A1:A700,A1) in cell B1 and copied down, entries which have a
space after the final letter are, quite correctly, treated as a different
word and therefore not included in the total for a particular word. IE:

[testing] and [testing ] are treated as different words. (brackets to show
the space)
How can I adapt the formula to ignore any space after the final letter in
the cell?

Thanks,

--
Traa Dy Liooar

Jock

--

Dave Peterson

COUNTIF question
 

In B1:

=if(sumproduct(--(trim(A$1:A1)=trim(A1)))1,"",
sumproduct(--(trim(A$1:A$700)=trim(A1))))

And copied down.



Jock wrote:

Thinking further, if a name were to appear, say six times, would it be
possible to only show the result aagainst the first entry rather than all
six? (The names are listed by fastest times in ascending order, not
alphabetically)
--
Traa Dy Liooar

Jock

"Dave Peterson" wrote:

I missed a note:

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html



Jock wrote:

With =COUNTIF(A1:A700,A1) in cell B1 and copied down, entries which have a
space after the final letter are, quite correctly, treated as a different
word and therefore not included in the total for a particular word. IE:

[testing] and [testing ] are treated as different words. (brackets to show
the space)
How can I adapt the formula to ignore any space after the final letter in
the cell?

Thanks,

--
Traa Dy Liooar

Jock

--

Dave Peterson


--

Dave Peterson

COUNTIF question
 

Surely that'll only show either 'blank' or '1'?
--
Traa Dy Liooar

Jock


"Dave Peterson" wrote:

In B1:

=if(sumproduct(--(trim(A$1:A1)=trim(A1)))1,"",
sumproduct(--(trim(A$1:A$700)=trim(A1))))

And copied down.



Jock wrote:

Thinking further, if a name were to appear, say six times, would it be
possible to only show the result aagainst the first entry rather than all
six? (The names are listed by fastest times in ascending order, not
alphabetically)
--
Traa Dy Liooar

Jock

"Dave Peterson" wrote:

I missed a note:

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html



Jock wrote:

With =COUNTIF(A1:A700,A1) in cell B1 and copied down, entries which have a
space after the final letter are, quite correctly, treated as a different
word and therefore not included in the total for a particular word. IE:

[testing] and [testing ] are treated as different words. (brackets to show
the space)
How can I adapt the formula to ignore any space after the final letter in
the cell?

Thanks,

--
Traa Dy Liooar

Jock

--

Dave Peterson


--

Dave Peterson

COUNTIF question
 

Then only use the formula in the first 6 cells????

Jock wrote:

Surely that'll only show either 'blank' or '1'?
--
Traa Dy Liooar

Jock

"Dave Peterson" wrote:

In B1:

=if(sumproduct(--(trim(A$1:A1)=trim(A1)))1,"",
sumproduct(--(trim(A$1:A$700)=trim(A1))))

And copied down.



Jock wrote:

Thinking further, if a name were to appear, say six times, would it be
possible to only show the result aagainst the first entry rather than all
six? (The names are listed by fastest times in ascending order, not
alphabetically)
--
Traa Dy Liooar

Jock

"Dave Peterson" wrote:

I missed a note:

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html



Jock wrote:

With =COUNTIF(A1:A700,A1) in cell B1 and copied down, entries which have a
space after the final letter are, quite correctly, treated as a different
word and therefore not included in the total for a particular word. IE:

[testing] and [testing ] are treated as different words. (brackets to show
the space)
How can I adapt the formula to ignore any space after the final letter in
the cell?

Thanks,

--
Traa Dy Liooar

Jock

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

COUNTIF question
 

I misread your point.

Try it first. Post back if it doesn't work correctly.

Jock wrote:

Surely that'll only show either 'blank' or '1'?
--
Traa Dy Liooar

Jock

"Dave Peterson" wrote:

In B1:

=if(sumproduct(--(trim(A$1:A1)=trim(A1)))1,"",
sumproduct(--(trim(A$1:A$700)=trim(A1))))

And copied down.



Jock wrote:

Thinking further, if a name were to appear, say six times, would it be
possible to only show the result aagainst the first entry rather than all
six? (The names are listed by fastest times in ascending order, not
alphabetically)
--
Traa Dy Liooar

Jock

"Dave Peterson" wrote:

I missed a note:

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html



Jock wrote:

With =COUNTIF(A1:A700,A1) in cell B1 and copied down, entries which have a
space after the final letter are, quite correctly, treated as a different
word and therefore not included in the total for a particular word. IE:

[testing] and [testing ] are treated as different words. (brackets to show
the space)
How can I adapt the formula to ignore any space after the final letter in
the cell?

Thanks,

--
Traa Dy Liooar

Jock

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

COUNTIF question
 

Have tried it but its either blank or 1. :(
--
tia


"Dave Peterson" wrote:

I misread your point.

Try it first. Post back if it doesn't work correctly.

Jock wrote:

Surely that'll only show either 'blank' or '1'?
--
Traa Dy Liooar

Jock

"Dave Peterson" wrote:

In B1:

=if(sumproduct(--(trim(A$1:A1)=trim(A1)))1,"",
sumproduct(--(trim(A$1:A$700)=trim(A1))))

And copied down.



Jock wrote:

Thinking further, if a name were to appear, say six times, would it be
possible to only show the result aagainst the first entry rather than all
six? (The names are listed by fastest times in ascending order, not
alphabetically)
--
Traa Dy Liooar

Jock

"Dave Peterson" wrote:

I missed a note:

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html



Jock wrote:

With =COUNTIF(A1:A700,A1) in cell B1 and copied down, entries which have a
space after the final letter are, quite correctly, treated as a different
word and therefore not included in the total for a particular word. IE:

[testing] and [testing ] are treated as different words. (brackets to show
the space)
How can I adapt the formula to ignore any space after the final letter in
the cell?

Thanks,

--
Traa Dy Liooar

Jock

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

COUNTIF question
 

I bet you changed the formula.

Either try it again (copy|paste directly from the newsgroup suggestion)

or post the formula that's failing.

JockW wrote:

Have tried it but its either blank or 1. :(
--
tia

"Dave Peterson" wrote:

I misread your point.

Try it first. Post back if it doesn't work correctly.

Jock wrote:

Surely that'll only show either 'blank' or '1'?
--
Traa Dy Liooar

Jock

"Dave Peterson" wrote:

In B1:

=if(sumproduct(--(trim(A$1:A1)=trim(A1)))1,"",
sumproduct(--(trim(A$1:A$700)=trim(A1))))

And copied down.



Jock wrote:

Thinking further, if a name were to appear, say six times, would it be
possible to only show the result aagainst the first entry rather than all
six? (The names are listed by fastest times in ascending order, not
alphabetically)
--
Traa Dy Liooar

Jock

"Dave Peterson" wrote:

I missed a note:

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html



Jock wrote:

With =COUNTIF(A1:A700,A1) in cell B1 and copied down, entries which have a
space after the final letter are, quite correctly, treated as a different
word and therefore not included in the total for a particular word. IE:

[testing] and [testing ] are treated as different words. (brackets to show
the space)
How can I adapt the formula to ignore any space after the final letter in
the cell?

Thanks,

--
Traa Dy Liooar

Jock

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

COUNTIF question
 

Luckily I'm not a betting man as, obviously being knowledgable in such
things, I did indeed tweak the formula and therefore c*cked it up!
Thanks Dave, works a treat now I've stopped fiddling with it.
--
Cheers


"Dave Peterson" wrote:

I bet you changed the formula.

Either try it again (copy|paste directly from the newsgroup suggestion)

or post the formula that's failing.

JockW wrote:

Have tried it but its either blank or 1. :(
--
tia

"Dave Peterson" wrote:

I misread your point.

Try it first. Post back if it doesn't work correctly.

Jock wrote:

Surely that'll only show either 'blank' or '1'?
--
Traa Dy Liooar

Jock

"Dave Peterson" wrote:

In B1:

=if(sumproduct(--(trim(A$1:A1)=trim(A1)))1,"",
sumproduct(--(trim(A$1:A$700)=trim(A1))))

And copied down.



Jock wrote:

Thinking further, if a name were to appear, say six times, would it be
possible to only show the result aagainst the first entry rather than all
six? (The names are listed by fastest times in ascending order, not
alphabetically)
--
Traa Dy Liooar

Jock

"Dave Peterson" wrote:

I missed a note:

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html



Jock wrote:

With =COUNTIF(A1:A700,A1) in cell B1 and copied down, entries which have a
space after the final letter are, quite correctly, treated as a different
word and therefore not included in the total for a particular word. IE:

[testing] and [testing ] are treated as different words. (brackets to show
the space)
How can I adapt the formula to ignore any space after the final letter in
the cell?

Thanks,

--
Traa Dy Liooar

Jock

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

COUNTIF question
 

My last post was blank for some reason; probably user incompetence.
Anyway, I had indeed 'fiddled' with the code and therefore c*cked it up!
Once I put it in 'as is' it worked fine.
Thanks Dave
--
Traa Dy Liooar

Jock


"Dave Peterson" wrote:

I bet you changed the formula.

Either try it again (copy|paste directly from the newsgroup suggestion)

or post the formula that's failing.

JockW wrote:

Have tried it but its either blank or 1. :(
--
tia

"Dave Peterson" wrote:

I misread your point.

Try it first. Post back if it doesn't work correctly.

Jock wrote:

Surely that'll only show either 'blank' or '1'?
--
Traa Dy Liooar

Jock

"Dave Peterson" wrote:

In B1:

=if(sumproduct(--(trim(A$1:A1)=trim(A1)))1,"",
sumproduct(--(trim(A$1:A$700)=trim(A1))))

And copied down.



Jock wrote:

Thinking further, if a name were to appear, say six times, would it be
possible to only show the result aagainst the first entry rather than all
six? (The names are listed by fastest times in ascending order, not
alphabetically)
--
Traa Dy Liooar

Jock

"Dave Peterson" wrote:

I missed a note:

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html



Jock wrote:

With =COUNTIF(A1:A700,A1) in cell B1 and copied down, entries which have a
space after the final letter are, quite correctly, treated as a different
word and therefore not included in the total for a particular word. IE:

[testing] and [testing ] are treated as different words. (brackets to show
the space)
How can I adapt the formula to ignore any space after the final letter in
the cell?

Thanks,

--
Traa Dy Liooar

Jock

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

COUNTIF question
 

It showed up ok for me--maybe that CDO interface is the problem????

Glad you got it working.

Jock wrote:

My last post was blank for some reason; probably user incompetence.
Anyway, I had indeed 'fiddled' with the code and therefore c*cked it up!
Once I put it in 'as is' it worked fine.
Thanks Dave
--
Traa Dy Liooar

Jock

"Dave Peterson" wrote:

I bet you changed the formula.

Either try it again (copy|paste directly from the newsgroup suggestion)

or post the formula that's failing.

JockW wrote:

Have tried it but its either blank or 1. :(
--
tia

"Dave Peterson" wrote:

I misread your point.

Try it first. Post back if it doesn't work correctly.

Jock wrote:

Surely that'll only show either 'blank' or '1'?
--
Traa Dy Liooar

Jock

"Dave Peterson" wrote:

In B1:

=if(sumproduct(--(trim(A$1:A1)=trim(A1)))1,"",
sumproduct(--(trim(A$1:A$700)=trim(A1))))

And copied down.



Jock wrote:

Thinking further, if a name were to appear, say six times, would it be
possible to only show the result aagainst the first entry rather than all
six? (The names are listed by fastest times in ascending order, not
alphabetically)
--
Traa Dy Liooar

Jock

"Dave Peterson" wrote:

I missed a note:

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html



Jock wrote:

With =COUNTIF(A1:A700,A1) in cell B1 and copied down, entries which have a
space after the final letter are, quite correctly, treated as a different
word and therefore not included in the total for a particular word. IE:

[testing] and [testing ] are treated as different words. (brackets to show
the space)
How can I adapt the formula to ignore any space after the final letter in
the cell?

Thanks,

--
Traa Dy Liooar

Jock

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson