Return Count for LAST NonBlank Cell in each Row

[Sam via OfficeKB.com]

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

 

[Domenic]

Try...

O5, copied down:

=10-IF(COUNTIF(C5:L5,"x"),LOOKUP(REPT("z",255),C5:L5,COLUMN(C5:L5)-COLUMN
(C5)+1))

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you for reply. I've tried your suggested Formula below:

=10-IF(COUNTIF(C5:L5,"x"),LOOKUP(REPT("z",255),C5:L5,COLUMN(C5:L5)-COLUMN
(C5)+1))

The Formula only produces the correct answer for Rows when there are:
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

 

[Aladin Akyurek]

=IF(COUNTIF(C5:L5,"x"),COLUMNS(C5:L5)-MATCH(2,1/(C5:L5="x")),0)

which needs to be confirmed with+control+enter.

 

[Domenic]

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!

 

[Domenic]

Sorry, I forgot to mention that the formula needs to be confirmed with
CONTROL+SHIFT+ENTER.

 

[Sam via OfficeKB.com]

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

 

[Sam via OfficeKB.com]

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

 

[Aladin Akyurek]

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,

 

[Sam via OfficeKB.com]

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

 

[Sam via OfficeKB.com]

Hi Domenic,

If you have the time, could you explain the various parts of the Formula.
Much appreciated.

=COLUMNS(C5:L5)-IF(COUNTIF(C5:L5,"x"),MATCH(2,1/(C5:L5="x")))

needs to be confirmed with
CONTROL+SHIFT+ENTER.

Thanks
Sam

 

[Aladin Akyurek]

COLUMNS(C5:L5)

returns the number of columns in C5:L5.

IF(COUNTIF(C5:L5,"x"),MATCH(2,1/(C5:L5="x")))

The condition part of IF consists of a conditional count. If the count
is zero (interpreted as FALSE), the IF bit as a whole will return FALSE
and the MATCH bit will be skipped. Thus when there is no x in C5:L5, we get:

10-FALSE ==> 10-0 ==> 10 (Math ops convert the truth values of TRUE and
FALSE into their numeric equivalents in Excel: 1 and 0, respectively.

When the COUNTIF bit (in the condition part of IF) returns a non-zero
count (a non-zero numeric result interpreted as TRUE), the MATCH bit is
evaluated.

The MATCH bit returns the position of the last x entry in C5:L5, which
is subtracted from the columns count. How does MATCH calculates the last
x's position is described here:

http://tinyurl.com/7q6cd

 

[Sam via OfficeKB.com]

Hi Aladin,

Thank you very much for explanation of Formula and pointer to
http://tinyurl.com/7q6cd.


Thanks again,
Sam