software

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: ={quot;aaa quot;,quot;bbb
quot;,quot;ccc quot;}
But this does not work. It always return values just with first parameter.
if formula is written as {=SUM(IF(A4:A100={quot;1A10 quot;,quot;1A11 quot;,quot;1A93
quot;},C4:C100,0))}, then it works but it is not what I need exactly.

Is it actually possible to work with more quot;parametersquot; in one single cell?

Thanks
Jan

Try this alternative

=SUMPRODUCT(--(ISNUMBER(MATCH(A4:A100,{quot;aaaquot;,quot;bbbquot;},0))),C4:C100 )

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Ischiasquot; gt; wrote in message
...
gt; Hi all,
gt; I am trying to get one formula work but no success.
gt; Has anybody solved it already?
gt;
gt; I have formula {=SUM(IF(A4:A100=D4,C4:C100,0))}
gt; and in the cell D4 I would like to have more parameters: ={quot;aaa quot;,quot;bbb
gt; quot;,quot;ccc quot;}
gt; But this does not work. It always return values just with first parameter.
gt; if formula is written as {=SUM(IF(A4:A100={quot;1A10 quot;,quot;1A11 quot;,quot;1A93
gt; quot;},C4:C100,0))}, then it works but it is not what I need exactly.
gt;
gt; Is it actually possible to work with more quot;parametersquot; in one single cell?
gt;
gt; Thanks
gt; Jan
I know but what I need is the possibility to input more separate
parameters in one cell

Bob Phillips wrote:
gt; Try this alternative
gt;
gt; =SUMPRODUCT(--(ISNUMBER(MATCH(A4:A100,{quot;aaaquot;,quot;bbbquot;},0))),C4:C100 )
gt;

well good luck ...

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Ischiasquot; gt; wrote in message
...
gt; I know but what I need is the possibility to input more separate
gt; parameters in one cell
gt;
gt; Bob Phillips wrote:
gt; gt; Try this alternative
gt; gt;
gt; gt; =SUMPRODUCT(--(ISNUMBER(MATCH(A4:A100,{quot;aaaquot;,quot;bbbquot;},0))),C4:C100 )
gt; gt;
If you let D46 contain 1A10, 1A11, and 1A93, then you can adopt Bob's
formula...

=SUMPRODUCT(--(ISNUMBER(MATCH(A4:A100,D46,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 gt; Name gt; Define

Name: Param

Refers to:

=MID(SUBSTITUTE('Sheet1'!$D$4,quot;,
quot;,quot;quot;),ROW(INDIRECT(quot;1:quot;amp;LEN(SUBSTITUTE('Sheet1'!$D $4,quot;,
quot;,quot;quot;))/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 gt;,
Ischias gt; wrote:

gt; Hi all,
gt; I am trying to get one formula work but no success.
gt; Has anybody solved it already?
gt;
gt; I have formula {=SUM(IF(A4:A100=D4,C4:C100,0))}
gt; and in the cell D4 I would like to have more parameters: ={quot;aaa quot;,quot;bbb
gt; quot;,quot;ccc quot;}
gt; But this does not work. It always return values just with first parameter.
gt; if formula is written as {=SUM(IF(A4:A100={quot;1A10 quot;,quot;1A11 quot;,quot;1A93
gt; quot;},C4:C100,0))}, then it works but it is not what I need exactly.
gt;
gt; Is it actually possible to work with more quot;parametersquot; in one single cell?
gt;
gt; Thanks
gt; 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 quot;View Codequot;. 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
{quot;aaa quot;,quot;bbb quot;,quot;ccc quot;}

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 ={quot;Aquot;,quot;Bquot;,quot;Cquot;} 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.

quot;Ischiasquot; wrote:

gt; Hi all,
gt; I am trying to get one formula work but no success.
gt; Has anybody solved it already?
gt;
gt; I have formula {=SUM(IF(A4:A100=D4,C4:C100,0))}
gt; and in the cell D4 I would like to have more parameters: ={quot;aaa quot;,quot;bbb
gt; quot;,quot;ccc quot;}
gt; But this does not work. It always return values just with first parameter.
gt; if formula is written as {=SUM(IF(A4:A100={quot;1A10 quot;,quot;1A11 quot;,quot;1A93
gt; quot;},C4:C100,0))}, then it works but it is not what I need exactly.
gt;
gt; Is it actually possible to work with more quot;parametersquot; in one single cell?
gt;
gt; Thanks
gt; Jan
gt;

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

quot;Slothquot; wrote:

gt; I think you will have to use a custom function. Here is one I called QWERTY.
gt; To insert this right click the sheet name and click quot;View Codequot;. Right
gt; Click on ThisWorkbook and click Insert Module. Copy and paste this code
gt; exactly...
gt;
gt; Function qwerty(rng As Range)
gt; If rng.Count = 1 Then
gt; qwerty = Evaluate(rng.Value)
gt; End If
gt; End Function
gt;
gt; Save and close the VBA window. Now change your formula to this
gt; {=SUM(IF(A4:A100=QWERTY(D4),C4:C100,0))}
gt; D4 should either be text only, or a formula that results in text; and should
gt; have a result that looks like this
gt; {quot;aaa quot;,quot;bbb quot;,quot;ccc quot;}
gt;
gt; To answer your question directly, I don't think Excell can handle an array
gt; as the result of a function in a single cell (ie ={quot;Aquot;,quot;Bquot;,quot;Cquot;} or ={1,2,3})
gt; whether you use enter or ctrl shift enter. In the help it describes how if
gt; you want to output the result of a formula that outputs an array you need to
gt; highlight the same number of cells as the number of arguments as the
gt; resulting array. Using the a,b,c example I gave, you would select A1:C1 and
gt; type ={“A”,”B”,”C”} and hit ctrl shift enter. This will output A in A1, B in
gt; B1, and C in C1. The wording of the explanation leads me to believe that
gt; Excell has no functionality to understand an array as a formula result for a
gt; single cell.
gt;
gt; NOTE: I am claiming credit for the code provided above, it was given to me
gt; by Bob Phillips in another discussion about another problem I was having.
gt;
gt; quot;Ischiasquot; wrote:
gt;
gt; gt; Hi all,
gt; gt; I am trying to get one formula work but no success.
gt; gt; Has anybody solved it already?
gt; gt;
gt; gt; I have formula {=SUM(IF(A4:A100=D4,C4:C100,0))}
gt; gt; and in the cell D4 I would like to have more parameters: ={quot;aaa quot;,quot;bbb
gt; gt; quot;,quot;ccc quot;}
gt; gt; But this does not work. It always return values just with first parameter.
gt; gt; if formula is written as {=SUM(IF(A4:A100={quot;1A10 quot;,quot;1A11 quot;,quot;1A93
gt; gt; quot;},C4:C100,0))}, then it works but it is not what I need exactly.
gt; gt;
gt; gt; Is it actually possible to work with more quot;parametersquot; in one single cell?
gt; gt;
gt; gt; Thanks
gt; gt; Jan
gt; gt;