SUMPRODUCT with trying to create array of non-blanks

[dan]

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

 

[Mike H]

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]

Thanks Mike,

That is perfect. Works like a charm. Sorry for the simple question, and
appreciate the super quick response!

-Dan

 

[Mike H]

Glad I could help

Thanks Mike,

That is perfect. Works like a charm. Sorry for the simple question, and
appreciate the super quick response!

-Dan

 

[Ashish Mathur]

Hi,

You can also use this

SUMPRODUCT((NOT(ISBLANK(B3:B8)))*(B3:B8))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com