Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default 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   Report Post  
Domenic
 
Posts: n/a
Default

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

  #3   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default

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
  #4   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

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

which needs to be confirmed with+control+enter.
  #5   Report Post  
Domenic
 
Posts: n/a
Default

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



  #6   Report Post  
Domenic
 
Posts: n/a
Default

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

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

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   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

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

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


  #11   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default

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

--
Message posted via http://www.officekb.com
  #12   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

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 he

http://tinyurl.com/7q6cd

Sam via OfficeKB.com wrote:
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

  #13   Report Post  
Sam via OfficeKB.com
 
Posts: n/a
Default

Hi Aladin,

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


Thanks again,
Sam

--
Message posted via http://www.officekb.com
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
"count if" function based on value of another cell Anauna Excel Worksheet Functions 3 February 24th 05 07:33 PM
I want to return "0" based on another cell CM Excel Worksheet Functions 2 February 17th 05 03:46 PM
formula to return the value of a cell based on a looked up true reference sarah Excel Worksheet Functions 2 February 2nd 05 09:15 PM
How to make a cell return the formatted value in a text string (i. n.almeida Excel Worksheet Functions 3 February 2nd 05 02:59 PM
Can vlookup return multiple matches in a single cell? cchristensen Excel Discussion (Misc queries) 3 December 2nd 04 02:56 AM


All times are GMT +1. The time now is 09:51 AM.

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

About Us

"It's about Microsoft Excel"