Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT with trying to create array of non-blanks
Hi...
I have two columns of information, and I want to add all elements in one where the corresponding elements are non-blanks on the other column. I know I can generate a new column that has a TRUE or 1 if a value in another column is nonblank, and then simply use that to multiply, and then do the sum. But I'm trying to do it in one go, and having problems, mainly arround getting an array that represents the nonblanks of one column. Psuedocode would be something like: SUMPRODUCT(A3:A8,ISBLANK(B3:B8)) But that second argument is the problem. I have tried using IF statements to see if that generated an element by element comparison but no success. I'm sure this is easy, but havn't been able to find something. Thanks ahead of time for your help, Dan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT with trying to create array of non-blanks
Dan,
You don't need a helper column you can simply do this =SUMPRODUCT((A3:A8<"")*(B3:B8)) Which sums B3 - B8 where there are data in the corresponding Column A Mike "dan" wrote: Hi... I have two columns of information, and I want to add all elements in one where the corresponding elements are non-blanks on the other column. I know I can generate a new column that has a TRUE or 1 if a value in another column is nonblank, and then simply use that to multiply, and then do the sum. But I'm trying to do it in one go, and having problems, mainly arround getting an array that represents the nonblanks of one column. Psuedocode would be something like: SUMPRODUCT(A3:A8,ISBLANK(B3:B8)) But that second argument is the problem. I have tried using IF statements to see if that generated an element by element comparison but no success. I'm sure this is easy, but havn't been able to find something. Thanks ahead of time for your help, Dan |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT with trying to create array of non-blanks
Thanks Mike,
That is perfect. Works like a charm. Sorry for the simple question, and appreciate the super quick response! -Dan "Mike H" wrote: Dan, You don't need a helper column you can simply do this =SUMPRODUCT((A3:A8<"")*(B3:B8)) Which sums B3 - B8 where there are data in the corresponding Column A Mike "dan" wrote: Hi... I have two columns of information, and I want to add all elements in one where the corresponding elements are non-blanks on the other column. I know I can generate a new column that has a TRUE or 1 if a value in another column is nonblank, and then simply use that to multiply, and then do the sum. But I'm trying to do it in one go, and having problems, mainly arround getting an array that represents the nonblanks of one column. Psuedocode would be something like: SUMPRODUCT(A3:A8,ISBLANK(B3:B8)) But that second argument is the problem. I have tried using IF statements to see if that generated an element by element comparison but no success. I'm sure this is easy, but havn't been able to find something. Thanks ahead of time for your help, Dan |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT with trying to create array of non-blanks
Glad I could help
"dan" wrote: Thanks Mike, That is perfect. Works like a charm. Sorry for the simple question, and appreciate the super quick response! -Dan "Mike H" wrote: Dan, You don't need a helper column you can simply do this =SUMPRODUCT((A3:A8<"")*(B3:B8)) Which sums B3 - B8 where there are data in the corresponding Column A Mike "dan" wrote: Hi... I have two columns of information, and I want to add all elements in one where the corresponding elements are non-blanks on the other column. I know I can generate a new column that has a TRUE or 1 if a value in another column is nonblank, and then simply use that to multiply, and then do the sum. But I'm trying to do it in one go, and having problems, mainly arround getting an array that represents the nonblanks of one column. Psuedocode would be something like: SUMPRODUCT(A3:A8,ISBLANK(B3:B8)) But that second argument is the problem. I have tried using IF statements to see if that generated an element by element comparison but no success. I'm sure this is easy, but havn't been able to find something. Thanks ahead of time for your help, Dan |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT with trying to create array of non-blanks
Hi,
You can also use this SUMPRODUCT((NOT(ISBLANK(B3:B8)))*(B3:B8)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "dan" wrote in message ... Hi... I have two columns of information, and I want to add all elements in one where the corresponding elements are non-blanks on the other column. I know I can generate a new column that has a TRUE or 1 if a value in another column is nonblank, and then simply use that to multiply, and then do the sum. But I'm trying to do it in one go, and having problems, mainly arround getting an array that represents the nonblanks of one column. Psuedocode would be something like: SUMPRODUCT(B3:B8,ISBLANK(B3:B8)) But that second argument is the problem. I have tried using IF statements to see if that generated an element by element comparison but no success. I'm sure this is easy, but havn't been able to find something. Thanks ahead of time for your help, Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with blanks and the numeric 0 | Excel Discussion (Misc queries) | |||
Counting non blanks with SUMPRODUCT? | Excel Discussion (Misc queries) | |||
Sumproduct copying blanks or how to insert zero into blanks | Excel Worksheet Functions | |||
Conditional Average Array with Dates, <blanks, 0 | Excel Worksheet Functions | |||
Sumproduct - Blanks | Excel Worksheet Functions |