Array function - I think!

[CES]

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

 

[Eduardo]

Hi,

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

 

[Eduardo]

OOpps, use this formula instead

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

 

[T. Valko]

See if this does what you want...

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

 

[Ziggy]

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

 

[CES]

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]

{=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)

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Ziggy]

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)

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