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

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

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

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

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