Remember Me?

 Sam via OfficeKB.com Posts: n/a 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

 Domenic Posts: n/a Try...

O5, copied down:

=10-IF(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

 Sam via OfficeKB.com Posts: n/a 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,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
 Aladin Akyurek Posts: n/a =IF(COUNTIF(C5:L5,"x"),COLUMNS(C5:L5)-MATCH(2,1/(C5:L5="x")),0)

which needs to be confirmed with+control+enter.
 Domenic Posts: n/a 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:

=10-IF(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

 Domenic Posts: n/a 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!

 Sam via OfficeKB.com Posts: n/a 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
 Sam via OfficeKB.com Posts: n/a 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
 Aladin Akyurek Posts: n/a 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,
 Sam via OfficeKB.com Posts: n/a 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

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Anauna Excel Worksheet Functions 3 February 24th 05 06:33 PM CM Excel Worksheet Functions 2 February 17th 05 02:46 PM sarah Excel Worksheet Functions 2 February 2nd 05 08:15 PM n.almeida Excel Worksheet Functions 3 February 2nd 05 01:59 PM cchristensen Excel Discussion (Misc queries) 3 December 2nd 04 01:56 AM

All times are GMT +1. The time now is 10:20 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd. Copyright ©2004-2021 ExcelBanter.
The comments are property of their posters.

# About Us

"It's about Microsoft Excel"

Copyright © 2017