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
|