Sum of unique values within a range with blank cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

loook - 12 May 2008 09:48 GMT

Hi, how can I sum unique values within a range where some cells are
blank?
An example:
A
1 100
2 150
3
4 150
5 50
6
7 100
8 100
9 50
10 SUM = 300 (100 + 150 + 50)

Thanx for any suggestion.

look

Reply to this Message

Bob Phillips - 12 May 2008 10:18 GMT

=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10&""),A1:A10)

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi, how can I sum unique values within a range where some cells are
> blank?
[quoted text clipped - 14 lines]
>
> look

Reply to this Message

Gary''s Student - 12 May 2008 10:23 GMT

In B1 enter:
=IF(COUNTIF($A$1:A1,A1)=1,A1,"") and copy down
In another cell:
=SUM(B:B)

Signature

Gary''s Student - gsnu200785

> Hi, how can I sum unique values within a range where some cells are
> blank?
[quoted text clipped - 14 lines]
>
> look

Reply to this Message

Teethless mama - 12 May 2008 15:11 GMT

=SUM(INDEX(1/COUNTIF(A1:A9,A1:A9&"")*A1:A9,0))

> Hi, how can I sum unique values within a range where some cells are
> blank?
[quoted text clipped - 14 lines]
>
> look

Reply to this Message