Excel function - countif
 

I am using the countif function to count all of the cells in a column (B1-B5)
that have the value "x."

=COUNTIF('Remote Offices'!B1:B5,"x")

I want to modify the formula, so that it returns to me the text in a
different column (A1-A5) if the condition is met. Is this possible?

For example if cells B1, B2, and B5 have the value "x" and cells
A1="Irvine", A2="Anaheim, and A5="Tustin", I would like the function to give
the following result:
"Irvine, Anaheim, Tustin"

Excel function - countif
 

Well, you could have a construct like this:

=IF('Remote Offices'!B1="x",'Remote Offices'!A1&", ","") & IF('Remote
Offices'!B2="x",'Remote Offices'!A2&", ","") & IF('Remote Offices'!
B3="x",'Remote Offices'!A3&", ","") & IF('Remote Offices'!
B4="x",'Remote Offices'!A4&", ","") & IF('Remote Offices'!
B5="x",'Remote Offices'!A5&", ","")

and you could continue in this way for several cells, but it is not
very elegant.

Hope this helps.

Pete

On Dec 8, 11:41*pm, Sanjay wrote:
I am using the countif function to count all of the cells in a column (B1-B5)
that have the value "x." *

=COUNTIF('Remote Offices'!B1:B5,"x")

I want to modify the formula, so that it returns to me the text in a
different column (A1-A5) if the condition is met. Is this possible?

For example if cells B1, B2, and B5 have the value "x" and cells
A1="Irvine", A2="Anaheim, and A5="Tustin", I would like the function to give
the following result:
"Irvine, Anaheim, Tustin"

Excel function - countif
 

That's called string concatenation and it's not a native function to Excel.
But you can add it if you're OK with enabling VBA in your worksheet.

http://www.excelforum.com/excel-prog...in-cell.html#2

That link to another forum included a copy of a UDF you can install in your
worksheet called CONCATIF(). It's used like a SUMIF() with a couple of extra
parameters. After you install the UDF, the formula for you would be:

=CONCATIF(B1:B5, "x", A1:A5, ",", TRUE)

--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.


"Sanjay" wrote:

I am using the countif function to count all of the cells in a column (B1-B5)
that have the value "x."

=COUNTIF('Remote Offices'!B1:B5,"x")

I want to modify the formula, so that it returns to me the text in a
different column (A1-A5) if the condition is met. Is this possible?

For example if cells B1, B2, and B5 have the value "x" and cells
A1="Irvine", A2="Anaheim, and A5="Tustin", I would like the function to give
the following result:
"Irvine, Anaheim, Tustin"