ExcelBanter - View Single Post

Ron Rosenfeld

On 28 Sep 2006 07:04:11 -0700, wrote:

For the cells that are in a different format, how do you wish to process them?
--ron

Yes, I'm banging my head on this.
I've got big majority of values in xx-yx format X-number, Y letter,
which is already correctly processed by the formula. Some are
xx-yyyyy+, which also gets through and some xx-y, the same without
error. Unfortunately I've got also 0, ', and empty cells. These,
especially empty cells, cause problems. It would be the best if these
got totally ignored.

What i did was combine the formula with COUNTIF(a1:a10;"*A*"), to count
the number of these occurrences, which also includes cells that only
contain this letter, but were not summed. I got much better result with
"*-A*", but I found at least one cell that had -A in the wrong place,
so it was also not calculated.

Is there an easy way to only use xx-yx format, also count the number of
those used and ignore 0 end empty? I don't seem to be writing my if
clauses correctly.

Thanks

I'm still not sure what you want to do with cells in the format of:

xxyy 55A1

xy-y... 5A-A123

xx-yyy... 55-ABCD

or even if those formats are possible.

The following *array* formula will sum the left two digits for any cells that
have the format of xx-A..... and ignore others.

If the left two characters are not digits, it will give an error message.

To enter an *array* formula, hold down <ctrl<shift while hitting <enter.
Excel will place braces {...} around the formula.

If the above is not what you want, you will have to be specific about the types
of inputs you might have and how you want to process the variations.

--ron