Array function - I think!

  • Thread starter Thread starter CES
  • Start date Start date
C

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
 
OOpps, use this formula instead

=SUMPRODUCT(--($X$1:$X$1000<>""),--($Y$1:$Y$1000<>""),$Y$1:$Y$1000)
 
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
 
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
 
{=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)
 
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)
Click to expand...

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