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

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!

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

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

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

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