Hi all,
I am trying to get one formula work but no success.
Has anybody solved it already?

I have formula {=SUM(IF(A4:A100=D4,C4:C100,0))}
and in the cell D4 I would like to have more parameters: ={"aaa ","bbb
","ccc "}
But this does not work. It always return values just with first parameter.
if formula is written as {=SUM(IF(A4:A100={"1A10 ","1A11 ","1A93
"},C4:C100,0))}, then it works but it is not what I need exactly.

Is it actually possible to work with more "parameters" in one single cell?

Thanks
Jan

Try this alternative

=SUMPRODUCT(--(ISNUMBER(MATCH(A4:A100,{"aaa","bbb"},0))),C4:C100 )

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ischias" wrote in message
...
Hi all,
I am trying to get one formula work but no success.
Has anybody solved it already?

I have formula {=SUM(IF(A4:A100=D4,C4:C100,0))}
and in the cell D4 I would like to have more parameters: ={"aaa ","bbb
","ccc "}
But this does not work. It always return values just with first parameter.
if formula is written as {=SUM(IF(A4:A100={"1A10 ","1A11 ","1A93
"},C4:C100,0))}, then it works but it is not what I need exactly.

Is it actually possible to work with more "parameters" in one single cell?

Thanks
Jan

I know but what I need is the possibility to input more separate
parameters in one cell

Bob Phillips wrote:
Try this alternative

=SUMPRODUCT(--(ISNUMBER(MATCH(A4:A100,{"aaa","bbb"},0))),C4:C100 )

well good luck ...

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ischias" wrote in message
...
I know but what I need is the possibility to input more separate
parameters in one cell

Bob Phillips wrote:
Try this alternative

=SUMPRODUCT(--(ISNUMBER(MATCH(A4:A100,{"aaa","bbb"},0))),C4:C100 )

If you let D4:D6 contain 1A10, 1A11, and 1A93, then you can adopt Bob's
formula...

=SUMPRODUCT(--(ISNUMBER(MATCH(A4:A100,D4:D6,0))),C4:C100)

Otherwise, if you absolutely want to list your criteria or parametres in
a single cell, for example D4, assuming that the each parametre is 4
characters in length and separated by a comma and space, try the
following...

Insert Name Define

Name: Param

Refers to:

=MID(SUBSTITUTE('Sheet1'!$D$4,",
",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE('Sheet1'!$D $4,",
",""))/4))*4-4+1,4)

**Change the sheet reference accordingly.

Click Ok

Then, use the following formula...

=SUMPRODUCT(--(ISNUMBER(MATCH(A4:A100,Param,0))),C4:C100)

If the parametres are not always 4 characters in length, or are not
separated by a comma and space, post back with a representative sample
of your data and I'll see if I can modify the formula.

Hope this helps!

In article ,
Ischias wrote:

Hi all,
I am trying to get one formula work but no success.
Has anybody solved it already?

I have formula {=SUM(IF(A4:A100=D4,C4:C100,0))}
and in the cell D4 I would like to have more parameters: ={"aaa ","bbb
","ccc "}
But this does not work. It always return values just with first parameter.
if formula is written as {=SUM(IF(A4:A100={"1A10 ","1A11 ","1A93
"},C4:C100,0))}, then it works but it is not what I need exactly.

Is it actually possible to work with more "parameters" in one single cell?

Thanks
Jan

I think you will have to use a custom function. Here is one I called QWERTY.
To insert this right click the sheet name and click "View Code". Right
Click on ThisWorkbook and click Insert Module. Copy and paste this code
exactly...

Function qwerty(rng As Range)
If rng.Count = 1 Then
qwerty = Evaluate(rng.Value)
End If
End Function

Save and close the VBA window. Now change your formula to this
{=SUM(IF(A4:A100=QWERTY(D4),C4:C100,0))}
D4 should either be text only, or a formula that results in text; and should
have a result that looks like this
{"aaa ","bbb ","ccc "}

To answer your question directly, I don't think Excell can handle an array
as the result of a function in a single cell (ie ={"A","B","C"} or ={1,2,3})
whether you use enter or ctrl+shift+enter. In the help it describes how if
you want to output the result of a formula that outputs an array you need to
highlight the same number of cells as the number of arguments as the
resulting array. Using the a,b,c example I gave, you would select A1:C1 and
type ={€œA€�,€�B€�,€�C€�} and hit ctrl+shift+enter. This will output A in A1, B in
B1, and C in C1. The wording of the explanation leads me to believe that
Excell has no functionality to understand an array as a formula result for a
single cell.

NOTE: I am claiming credit for the code provided above, it was given to me
by Bob Phillips in another discussion about another problem I was having.

"Ischias" wrote:

Hi all,
I am trying to get one formula work but no success.
Has anybody solved it already?

I have formula {=SUM(IF(A4:A100=D4,C4:C100,0))}
and in the cell D4 I would like to have more parameters: ={"aaa ","bbb
","ccc "}
But this does not work. It always return values just with first parameter.
if formula is written as {=SUM(IF(A4:A100={"1A10 ","1A11 ","1A93
"},C4:C100,0))}, then it works but it is not what I need exactly.

Is it actually possible to work with more "parameters" in one single cell?

Thanks
Jan

Oops, obviously my NOTE should read "I am NOT claiming credit..."

"Sloth" wrote:

I think you will have to use a custom function. Here is one I called QWERTY.
To insert this right click the sheet name and click "View Code". Right
Click on ThisWorkbook and click Insert Module. Copy and paste this code
exactly...

Function qwerty(rng As Range)
If rng.Count = 1 Then
qwerty = Evaluate(rng.Value)
End If
End Function

Save and close the VBA window. Now change your formula to this
{=SUM(IF(A4:A100=QWERTY(D4),C4:C100,0))}
D4 should either be text only, or a formula that results in text; and should
have a result that looks like this
{"aaa ","bbb ","ccc "}

To answer your question directly, I don't think Excell can handle an array
as the result of a function in a single cell (ie ={"A","B","C"} or ={1,2,3})
whether you use enter or ctrl+shift+enter. In the help it describes how if
you want to output the result of a formula that outputs an array you need to
highlight the same number of cells as the number of arguments as the
resulting array. Using the a,b,c example I gave, you would select A1:C1 and
type ={€œA€�,€�B€�,€�C€�} and hit ctrl+shift+enter. This will output A in A1, B in
B1, and C in C1. The wording of the explanation leads me to believe that
Excell has no functionality to understand an array as a formula result for a
single cell.

NOTE: I am claiming credit for the code provided above, it was given to me
by Bob Phillips in another discussion about another problem I was having.

"Ischias" wrote:

Hi all,
I am trying to get one formula work but no success.
Has anybody solved it already?

I have formula {=SUM(IF(A4:A100=D4,C4:C100,0))}
and in the cell D4 I would like to have more parameters: ={"aaa ","bbb
","ccc "}
But this does not work. It always return values just with first parameter.
if formula is written as {=SUM(IF(A4:A100={"1A10 ","1A11 ","1A93
"},C4:C100,0))}, then it works but it is not what I need exactly.

Is it actually possible to work with more "parameters" in one single cell?

Thanks
Jan