COUNTIF question

[Jock]

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,

 

[Stefi]

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

Regards,
Stefi


„Jock†ezt írta:

 

[Dave Peterson]

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

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,

 

[Dave Peterson]

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

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,

 

[Jock]

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

Jock

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

 

[Jock]

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

Jock

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

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,

 

[Jock]

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

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

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,

 

[Dave Peterson]

In B1:

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

And copied down.

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

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

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,

 

[Jock]

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

Jock

In B1:

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

And copied down.

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

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 here:
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,

 

[Dave Peterson]

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

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

Jock

In B1:

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

And copied down.

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

:

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 here:
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,

 

[Dave Peterson]

I misread your point.

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

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

Jock

In B1:

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

And copied down.

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

:

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 here:
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,

 

[JockW]

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

I misread your point.

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

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

Jock

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

:

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 here:
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,

 

[Dave Peterson]

I bet you changed the formula.

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

or post the formula that's failing.

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

I misread your point.

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

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

Jock

:

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

:

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 here:
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,

 

[JockW]

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

I bet you changed the formula.

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

or post the formula that's failing.

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

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

:

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

:

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 here:
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,

 

[Jock]

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

I bet you changed the formula.

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

or post the formula that's failing.

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

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

:

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

:

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 here:
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,

 

[Dave Peterson]

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

Glad you got it working.

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

I bet you changed the formula.

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

or post the formula that's failing.

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

:

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

:

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

:

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 here:
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,