Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF question | Excel Worksheet Functions | |||
countif question | Excel Discussion (Misc queries) | |||
countif question | Excel Worksheet Functions | |||
countif question | Excel Worksheet Functions | |||
COUNTIF Question | Excel Worksheet Functions |