How to read from concatenate ?

[Rocky Rodriguez]

I have a situation in excel. The case is as follow;

Column A (contain 10 rows with a single digit each)
Column B (contain 10 rows with a single digit each)

I use Column C to concatenate Column A and Column B.
Ex: (=CONCATENATE(A2,B2), =CONCATENATE(A3,B3), =CONCATENATE(A4,B4), ... =CONCATENATE(GA11,B11))

In cell C13 and C14, I try to use the formulas below to read from the concatenate formula (updating automatically) but unfortunately it doesn't work.
=COUNTIF($C$2:$C$11,"<="&50)
=COUNTIF($C$2:$C$11,">="&50)

Please, what should I do to make cells C13 and C14 abstract the values from the concatenate formula ?

This is a view of that:

 

[NARAYANK991]

Hi ,

The result of a concatenate is a text string ; in your COUNTIF you are checking against 50 which is a number.

The COUNTIF function itself will accept a number or a text string which evaluates to a number , but when it compares what is a number with text , that comparison will fail.

You can use two methods to convert the single digit values in columns A and B to numbers :

1. Use normal arithmetic to get a two digit number ; use the formula :

=A2 * 10 + B2

Copy this down.

2. Convert the concatenated value to a number ; use the formula :

=(A2 & B2) + 0

With either of these two methods , your COUNTIF will work.

Narayan

 

[Hui]

Rocky

Firstly, Welcome to the Chandoo.org Forums

If C2 and C11 actually are valid references to cells
Say C2 contains H11 and C11 contains H25

You could use something like:
=COUNTIF(indirect($C$2&":"&$C$11),"<="&50)

It is a shame your picture didn't post, it may have been too large

ps: I was the manager at a mine many years ago called "Rocky's Reward"