Counts the total number of cells with the mentioned letters, but then just divides it by 6. This will not return the value I need.
=(COUNTA(A1:T1)-SUM(COUNTIF(A1:T1,"v"),COUNTIF(A1:T1,"U")))/6
Example: in a row there are three groups of 6 cells containing "d", "a" or "n", and one group of 7. Your formula would return 4.166667 ((6+6+6+7)/6). However, I need it to return either 3 or 1 (depending wether I'm looking for groups of 6 or 7).
fyi, the above formula, counts the number of non-blank cells minus (-) cells that contains "v" & cells that contains "U".=(COUNTA(A1:T1)-SUM(COUNTIF(A1:T1,"v"),COUNTIF(A1:T1,"U")))/6
In your example, if there are 20 cells (i.e. A1:T1), 6 cells are blank, 10 cells contain "d", 2 cells contain "v" and 2 cells contain "U",
so:
= (14 cells that contains text - (2 "v" + 2 "U"))/ 6 = 1.7
can you post an example file and the expected results?
Last edited by corinereyes; 11-07-2007 at 04:44 PM.
Corine
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks