Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 . |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 . |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Function? | Excel Worksheet Functions | |||
OR function in array-entered IF function | Excel Worksheet Functions | |||
AND in an array function? | Excel Worksheet Functions | |||
Is there an array function or something like it? | Excel Worksheet Functions | |||
Array Function | Excel Worksheet Functions |