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
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 |
#4
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 |
#5
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 |
#6
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 |
#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 |
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 |