Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
concatenate in array formula?
I have an array formula that sums cells in column C if their corresponding
label in column B matches a desired string in cell A1. {=SUM(IF($A$1=$B$1:$B$9,$C$1:$C$9,0))} It works fine. But if I try to concatenate instead of sum (using Ctrl+Shift+Enter), this fails: {=CONCATENATE(IF($A$1=$B$1:$B$3,$D$1:$D$3,""))} Evaluating the formula yields CONCATENATE(IF({FALSE,TRUE,FALSE},$D$1:$D$3,"")) which evaluates to CONCATENATE(""), even though I got one match, and cell D2 contains the string "West". Can concatenation be done in an array formula? -- Squirl Girl |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
concatenate in array formula?
On Nov 5, 4:13*pm, Squirl Girl wrote:
I have an array formula that sums cells in column C if their corresponding label in column B matches a desired string in cell A1. {=SUM(IF($A$1=$B$1:$B$9,$C$1:$C$9,0))} It works fine. *But if I try to concatenate instead of sum (using Ctrl+Shift+Enter), this fails: {=CONCATENATE(IF($A$1=$B$1:$B$3,$D$1:$D$3,""))} Evaluating the formula yields CONCATENATE(IF({FALSE,TRUE,FALSE},$D$1:$D$3,"")) which evaluates to CONCATENATE(""), even though I got one match, and cell D2 contains the string "West". Can concatenation be done in an array formula? -- Squirl Girl Not with the built-in functions. A UDF could do it. I think I've seen such a UDF posted here. Otherwise, there are sets of UDFs on the web that could be downloaded for free. Morefunc includes one called MCONCAT, or look he http://www.cpearson.com/Excel/stringconcatenation.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula & Concatenate | Excel Worksheet Functions | |||
CONCATENATE in Array formula | Excel Discussion (Misc queries) | |||
How to concatenate an array of four stings using formulae (not UDF) | Excel Worksheet Functions | |||
Array Formula with Concatenate and If | Excel Worksheet Functions | |||
Concatenate an array | Excel Discussion (Misc queries) |