sum of cells that correspond to non empty cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

nada - 09 Mar 2008 09:04 GMT

hello,
if i have two columns and i want to sum the values in each cell in each
column that correspond to a nonempty cell in the other column what should i
do?

Reply to this Message

Lars-Åke Aspelin - 09 Mar 2008 09:17 GMT

>hello,
>if i have two columns and i want to sum the values in each cell in each
>column that correspond to a nonempty cell in the other column what should i
>do?

One of serveral possibilities is to use the SUMPRODUCT function

=SUMPRODUCT(- - (A1:A100<>""), B1:B100)

will sum the data for the first 100 rows of column B where the
corresponding cell i column A is non empty

Hope this helps.

Reply to this Message

Mike H - 09 Mar 2008 09:19 GMT

You could do this

=SUMPRODUCT((ISBLANK(A1:A30))*(B1:B30))

or this depending on your definition of blank
=SUMPRODUCT((A1:A30="")*(B1:B30))

Mike

> hello,
> if i have two columns and i want to sum the values in each cell in each
> column that correspond to a nonempty cell in the other column what should i
> do?

Reply to this Message

Mike H - 09 Mar 2008 09:21 GMT

have I misread your post you want to sum for non empty cells

=SUMPRODUCT((A1:A30<>"")*(B1:B30))

Mike

> hello,
> if i have two columns and i want to sum the values in each cell in each
> column that correspond to a nonempty cell in the other column what should i
> do?

Reply to this Message

Teethless mama - 09 Mar 2008 14:04 GMT

=SUMIF(A1:A100,"<>",B1:B100)

> hello,
> if i have two columns and i want to sum the values in each cell in each
> column that correspond to a nonempty cell in the other column what should i
> do?

Reply to this Message