Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting count
Sheet1
Column A "This is my string1" This is my string2 This is my string1 again This is my string3 Hey This is my string1 string1 .... .... Sheet2 ColumnA ColumnB No of times the text string1 appeared in sheet1 3 No of times the text string2 appeared in sheet1 1 No of times the text string3 appeared in sheet1 1 Can you help me create columnB in sheet2? If a particular string, say, string 1, appears multiple times in the same row, the count increments only by 1, not by the number of occurences in a particular row. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting count
In sheet2, assuming you have "String1" in column A1, enter in A2:
=SUMPRODUCT(--ISNUM(FIND(A2;Sheet1!$A$1:$A$5))) HTH -- AP a écrit dans le message de oups.com... Sheet1 Column A "This is my string1" This is my string2 This is my string1 again This is my string3 Hey This is my string1 string1 ... ... Sheet2 ColumnA ColumnB No of times the text string1 appeared in sheet1 3 No of times the text string2 appeared in sheet1 1 No of times the text string3 appeared in sheet1 1 Can you help me create columnB in sheet2? If a particular string, say, string 1, appears multiple times in the same row, the count increments only by 1, not by the number of occurences in a particular row. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting count
A little change.
Sheet1 Column A ColumnB "This is my string1" Count This is my string2 Count This is my string1 again Don't Count This is my string3 Count Hey This is my string1 string1 Count Sheet2 ColumnA ColumnB No of times the text string1 appeared in sheet1 2 No of times the text string2 appeared in sheet1 1 No of times the text string3 appeared in sheet1 1 If columnB in sheet 1 is "count", only then the stingx in columnA will be counted. What should my formula be now? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting count
=SUMPRODUCT(ISNUM(FIND(A2;Sheet1!$A$1:$A$5))*(Shee t1!$B$1:$B$5="Count"))
HTH -- AP a écrit dans le message de oups.com... A little change. Sheet1 Column A ColumnB "This is my string1" Count This is my string2 Count This is my string1 again Don't Count This is my string3 Count Hey This is my string1 string1 Count Sheet2 ColumnA ColumnB No of times the text string1 appeared in sheet1 2 No of times the text string2 appeared in sheet1 1 No of times the text string3 appeared in sheet1 1 If columnB in sheet 1 is "count", only then the stingx in columnA will be counted. What should my formula be now? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions |