Home 
Search 
Today's Posts 
#1




Return Count for LAST NonBlank Cell in each Row
Hi All,
I would like to find the LAST NonBlank TEXT Cell in a Row and subtract the Column number of the Cell from the total number of Columns in the Row. Or, if feasible to Count back from the LAST Column in my data Range  Column "L" and SUM the Count of Blank Cells to the very FIRST NonBlank Cell in each Row. Counting from Right to Left? The Expected Results below is the value required for each Row. Or, a more suitable calculation/Formula to provide the Expected Results below. The Row consists of 10 Columns  from "C" to "L", first Row is Row 5. The individual Cells either house an "x" or is blank (empty). My data Range: 1st Column = "C" 2nd Column = "D" 3rd Column = "E" 4th Column = "F" 5th Column = "G" 6th Column = "H" 7th Column = "I" 8th Column = "J" 9th Column = "K" 10th Column= "L" (LAST Column) Data: Row5 "x" in Column C, G, H Row6 "x" in Column K, L Row7 "x" in Column K Row8 "x" in Column G Row9 "x" in Column L Row10"x" in Column C Row11"x" in Column C, G, I Row12"x" in Column  (no x's in this Row) Calculation Required: Find last NonBlank Cell in each Row, then subtract that cells Column Number from Last Column Number in data Range. Row5 = Column L (10) minus Column H (6) = 4 Row6 = Column L (10) minus Column L (10)= 0 Row7 = Column L (10) minus Column K (9) = 1 Row12= Column L (10) minus Column ? = 10 (no x's in this Row) Expected Results (Returned in Column "O" relevant Row Number): Row5 = 4 Row6 = 0 Row7 = 1 Row8 = 5 Row9 = 0 Row10= 9 Row11= 3 Row12=10 Assistance very much appreciated. Regards, Sam  Message posted via http://www.officekb.com 
#2




Try...
O5, copied down: =10IF(COUNTIF(C5:L5,"x"),LOOKUP(REPT("z",255),C5:L5,C OLUMN(C5:L5)COLUMN (C5)+1)) Hope this helps! In article , "Sam via OfficeKB.com" wrote: Hi All, I would like to find the LAST NonBlank TEXT Cell in a Row and subtract the Column number of the Cell from the total number of Columns in the Row. Or, if feasible to Count back from the LAST Column in my data Range  Column "L" and SUM the Count of Blank Cells to the very FIRST NonBlank Cell in each Row. Counting from Right to Left? The Expected Results below is the value required for each Row. Or, a more suitable calculation/Formula to provide the Expected Results below. The Row consists of 10 Columns  from "C" to "L", first Row is Row 5. The individual Cells either house an "x" or is blank (empty). My data Range: 1st Column = "C" 2nd Column = "D" 3rd Column = "E" 4th Column = "F" 5th Column = "G" 6th Column = "H" 7th Column = "I" 8th Column = "J" 9th Column = "K" 10th Column= "L" (LAST Column) Data: Row5 "x" in Column C, G, H Row6 "x" in Column K, L Row7 "x" in Column K Row8 "x" in Column G Row9 "x" in Column L Row10"x" in Column C Row11"x" in Column C, G, I Row12"x" in Column  (no x's in this Row) Calculation Required: Find last NonBlank Cell in each Row, then subtract that cells Column Number from Last Column Number in data Range. Row5 = Column L (10) minus Column H (6) = 4 Row6 = Column L (10) minus Column L (10)= 0 Row7 = Column L (10) minus Column K (9) = 1 Row12= Column L (10) minus Column ? = 10 (no x's in this Row) Expected Results (Returned in Column "O" relevant Row Number): Row5 = 4 Row6 = 0 Row7 = 1 Row8 = 5 Row9 = 0 Row10= 9 Row11= 3 Row12=10 Assistance very much appreciated. Regards, Sam 
#3




Hi Domenic,
Thank you for reply. I've tried your suggested Formula below: =10IF(COUNTIF(C5:L5,"x"),LOOKUP(REPT("z",255),C5:L5,C OLUMN(C5:L5)COLUMN (C5)+1)) The Formula only produces the correct answer for Rows when there a No x's  returns correct answer of 10 or when an "x" is in last column  Column L  returns correct answer of 0. The Formula returns an incorrect 0 (zero) if the LAST "x" in the Row is in any other column position besides the LAST Column "L". From the example data below  the Formula should produce 1 (one) in Row 7 but instead it returns an incorrect 0 (zero). The Row consists of 10 Columns  from "C" to "L", first Row is Row 5. The individual Cells either house an "x" or is blank (empty). My data Range: 1st Column = "C" 2nd Column = "D" 3rd Column = "E" 4th Column = "F" 5th Column = "G" 6th Column = "H" 7th Column = "I" 8th Column = "J" 9th Column = "K" 10th Column= "L" (LAST Column) Data: Row5 "x" in Column C, G, H Row6 "x" in Column K, L Row7 "x" in Column K Row8 "x" in Column G Row9 "x" in Column L Row10"x" in Column C Row11"x" in Column C, G, I Row12"x" in Column  (no x's in this Row) Calculation Required: Find last NonBlank Cell in each Row, then subtract that cells Column Number from Last Column Number in data Range. Row5 = Column L (10) minus Column H (6) = 4 Row6 = Column L (10) minus Column L (10)= 0 Row7 = Column L (10) minus Column K (9) = 1 Row12= Column L (10) minus Column ? = 10 (no x's in this Row) Expected Results (Returned in Column "O" relevant Row Number): Row5 = 4 Row6 = 0 Row7 = 1 Row8 = 5 Row9 = 0 Row10= 9 Row11= 3 Row12=10 Can you see a way to tweak the Formula to provide the correct count for each Row? Further assistance appreciated. Thanks Sam  Message posted via http://www.officekb.com 
#4




=IF(COUNTIF(C5:L5,"x"),COLUMNS(C5:L5)MATCH(2,1/(C5:L5="x")),0)
which needs to be confirmed with+control+enter. 
#5




Hi Sam,
That's probably because your formulas within your range of cells return a "" (null string) when their particular condition evaluates to false. In this case, we can tweak Aladin's formula... =COLUMNS(C5:L5)IF(COUNTIF(C5:L5,"x"),MATCH(2,1/(C5:L5="x"))) Hope this helps! In article , "Sam via OfficeKB.com" wrote: Hi Domenic, Thank you for reply. I've tried your suggested Formula below: =10IF(COUNTIF(C5:L5,"x"),LOOKUP(REPT("z",255),C5:L5,C OLUMN(C5:L5)COLUMN (C5)+1)) The Formula only produces the correct answer for Rows when there a No x's  returns correct answer of 10 or when an "x" is in last column  Column L  returns correct answer of 0. The Formula returns an incorrect 0 (zero) if the LAST "x" in the Row is in any other column position besides the LAST Column "L". From the example data below  the Formula should produce 1 (one) in Row 7 but instead it returns an incorrect 0 (zero). The Row consists of 10 Columns  from "C" to "L", first Row is Row 5. The individual Cells either house an "x" or is blank (empty). My data Range: 1st Column = "C" 2nd Column = "D" 3rd Column = "E" 4th Column = "F" 5th Column = "G" 6th Column = "H" 7th Column = "I" 8th Column = "J" 9th Column = "K" 10th Column= "L" (LAST Column) Data: Row5 "x" in Column C, G, H Row6 "x" in Column K, L Row7 "x" in Column K Row8 "x" in Column G Row9 "x" in Column L Row10"x" in Column C Row11"x" in Column C, G, I Row12"x" in Column  (no x's in this Row) Calculation Required: Find last NonBlank Cell in each Row, then subtract that cells Column Number from Last Column Number in data Range. Row5 = Column L (10) minus Column H (6) = 4 Row6 = Column L (10) minus Column L (10)= 0 Row7 = Column L (10) minus Column K (9) = 1 Row12= Column L (10) minus Column ? = 10 (no x's in this Row) Expected Results (Returned in Column "O" relevant Row Number): Row5 = 4 Row6 = 0 Row7 = 1 Row8 = 5 Row9 = 0 Row10= 9 Row11= 3 Row12=10 Can you see a way to tweak the Formula to provide the correct count for each Row? Further assistance appreciated. Thanks Sam 
#6




Sorry, I forgot to mention that the formula needs to be confirmed with
CONTROL+SHIFT+ENTER. In article , Domenic wrote: Hi Sam, That's probably because your formulas within your range of cells return a "" (null string) when their particular condition evaluates to false. In this case, we can tweak Aladin's formula... =COLUMNS(C5:L5)IF(COUNTIF(C5:L5,"x"),MATCH(2,1/(C5:L5="x"))) Hope this helps! 
#7




Hi Aladin,
Thank you for reply. The Formula returns the correct result for all Rows where there is an "x" in one or more columns. Unfortunately, where there are no "x"'s in the Row it should return a count of 10 but returns 0 (zero). =IF(COUNTIF(C5:L5,"x"),COLUMNS(C5:L5)MATCH(2,1/(C5:L5="x")),0) which needs to be confirmed with+control+enter Any further help much appreciated. Thanks Sam  Message posted via http://www.officekb.com 
#8




Hi Domenic,
Thank you for further assistance. =COLUMNS(C5:L5)IF(COUNTIF(C5:L5,"x"),MATCH(2,1/(C5:L5="x"))) Your tweaked version of Aladin's Formula has provided the correct results for all Rows including the Rows where there is no "x". Much appreciated. Thanks Sam  Message posted via http://www.officekb.com 
#9




Sam via OfficeKB.com wrote:
Hi Aladin, Thank you for reply. The Formula returns the correct result for all Rows where there is an "x" in one or more columns. Unfortunately, where there are no "x"'s in the Row it should return a count of 10 but returns 0 (zero). =IF(COUNTIF(C5:L5,"x"),COLUMNS(C5:L5)MATCH(2,1/(C5:L5="x")),0) which needs to be confirmed with+control+enter Any further help much appreciated. Thanks Sam I didn't notice you you wanted 10 for that. See the tweaked version in Domenic's reply, 
#10




Hi Aladin,
Domenic has provided a tweaked version of your Formula and it provides the correct results for all Rows including those without "x"'s. =COLUMNS(C5:L5)IF(COUNTIF(C5:L5,"x"),MATCH(2,1/(C5:L5="x"))) Much appreciated. Thank you for assistance. Thanks Sam  Message posted via http://www.officekb.com 
Reply 

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
"count if" function based on value of another cell  Excel Worksheet Functions  
I want to return "0" based on another cell  Excel Worksheet Functions  
formula to return the value of a cell based on a looked up true reference  Excel Worksheet Functions  
How to make a cell return the formatted value in a text string (i.  Excel Worksheet Functions  
Can vlookup return multiple matches in a single cell?  Excel Discussion (Misc queries) 