Array function - I think!
 

I am trying to get a formula to return a number if two other cells have
values. Column X might have a number or a letter; Column Y will have a
number. For a particular line, if there is something in Column X and Column
Y, I want it to return the value of the number in column Y. I can do this
with a simple IF function, but the problem I'm having is that I need the cell
to add the value of every line where there is something in Column X and
Column Y. I tried making an array formula but it did not work:
=SUM((ISLOGICAL(H20:H403))*(ISLOGICAL (M20:M403))*(M20:M403))
Help!

Cynthia

Array function - I think!
 

Hi,

=sumproduct(($X$1:$X$1000<""),($Y$1:$Y$1000<""), $Y$1:$Y$1000)

"CES" wrote:

I am trying to get a formula to return a number if two other cells have
values. Column X might have a number or a letter; Column Y will have a
number. For a particular line, if there is something in Column X and Column
Y, I want it to return the value of the number in column Y. I can do this
with a simple IF function, but the problem I'm having is that I need the cell
to add the value of every line where there is something in Column X and
Column Y. I tried making an array formula but it did not work:
=SUM((ISLOGICAL(H20:H403))*(ISLOGICAL (M20:M403))*(M20:M403))
Help!

Cynthia

Array function - I think!
 

OOpps, use this formula instead

=SUMPRODUCT(--($X$1:$X$1000<""),--($Y$1:$Y$1000<""),$Y$1:$Y$1000)

"CES" wrote:

I am trying to get a formula to return a number if two other cells have
values. Column X might have a number or a letter; Column Y will have a
number. For a particular line, if there is something in Column X and Column
Y, I want it to return the value of the number in column Y. I can do this
with a simple IF function, but the problem I'm having is that I need the cell
to add the value of every line where there is something in Column X and
Column Y. I tried making an array formula but it did not work:
=SUM((ISLOGICAL(H20:H403))*(ISLOGICAL (M20:M403))*(M20:M403))
Help!

Cynthia

Array function - I think!
 

See if this does what you want...

=SUMIF(H20:H403,"<",M20:M403)

--
Biff
Microsoft Excel MVP


"CES" wrote in message
...
I am trying to get a formula to return a number if two other cells have
values. Column X might have a number or a letter; Column Y will have a
number. For a particular line, if there is something in Column X and
Column
Y, I want it to return the value of the number in column Y. I can do this
with a simple IF function, but the problem I'm having is that I need the
cell
to add the value of every line where there is something in Column X and
Column Y. I tried making an array formula but it did not work:
=SUM((ISLOGICAL(H20:H403))*(ISLOGICAL (M20:M403))*(M20:M403))
Help!

Cynthia

Array function - I think!
 

Here is an array formula that works.

Data

100 300
100 300
100 300
100
100
100
100
100 300
100 300

500 Result

{=SUM((I12:I20 <"")*(J12:J20<"")*I12:I20)}

Enter as Array with Ctrl-Shift-Enter

Siegfried

Array function - I think!
 

Yes - thanks so much! Simple and direct and better than me creating over 400
IF functions for a cell (which is what I was doing).

Cynthia

"T. Valko" wrote:

See if this does what you want...

=SUMIF(H20:H403,"<",M20:M403)

--
Biff
Microsoft Excel MVP


"CES" wrote in message
...
I am trying to get a formula to return a number if two other cells have
values. Column X might have a number or a letter; Column Y will have a
number. For a particular line, if there is something in Column X and
Column
Y, I want it to return the value of the number in column Y. I can do this
with a simple IF function, but the problem I'm having is that I need the
cell
to add the value of every line where there is something in Column X and
Column Y. I tried making an array formula but it did not work:
=SUM((ISLOGICAL(H20:H403))*(ISLOGICAL (M20:M403))*(M20:M403))
Help!

Cynthia



.

Array function - I think!
 

{=SUM((I12:I20 <"")*(J12:J20<"")*I12:I20)}

Why test for I12:I20 <"" ?

If a cell in col I is empty it will evaluate as 0:

0*(J12<"")*I12 = 0

=SUM((J12:J20<"")*I12:I20)

--
Biff
Microsoft Excel MVP


"Ziggy" wrote in message
...

Here is an array formula that works.

Data

100 300
100 300
100 300
100
100
100
100
100 300
100 300

500 Result

{=SUM((I12:I20 <"")*(J12:J20<"")*I12:I20)}

Enter as Array with Ctrl-Shift-Enter

Siegfried

Array function - I think!
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"CES" wrote in message
...
Yes - thanks so much! Simple and direct and better than me creating over
400
IF functions for a cell (which is what I was doing).

Cynthia

"T. Valko" wrote:

See if this does what you want...

=SUMIF(H20:H403,"<",M20:M403)

--
Biff
Microsoft Excel MVP


"CES" wrote in message
...
I am trying to get a formula to return a number if two other cells have
values. Column X might have a number or a letter; Column Y will have a
number. For a particular line, if there is something in Column X and
Column
Y, I want it to return the value of the number in column Y. I can do
this
with a simple IF function, but the problem I'm having is that I need
the
cell
to add the value of every line where there is something in Column X and
Column Y. I tried making an array formula but it did not work:
=SUM((ISLOGICAL(H20:H403))*(ISLOGICAL (M20:M403))*(M20:M403))
Help!

Cynthia



.

Array function - I think!
 

On Mar 30, 10:54*am, "T. Valko" wrote:
{=SUM((I12:I20 <"")*(J12:J20<"")*I12:I20)}

Why test for I12:I20 <"" ?

If a cell in col I is empty it will evaluate as 0:

0*(J12<"")*I12 = 0

=SUM((J12:J20<"")*I12:I20)

--
Biff
Microsoft Excel MVP


You're right. Old habits are hard to break.