CODEWITHSUNDEEP
arraysexcelsum
Gunnar Sjúrðarson Knudsen
Gunnar Sjúrðarson Knudsen

Reputation: 441

excel different SUM.IF array function

I have a very specific report, which I can't change the layout. Basicly, it looks like this:

   A     B       C
1  ASD   Other   10
2  ASD   Other   10
3  ASD   Other   10
4  JKL   Other   20
5  EFG   Other   5
6  EFG   Other   5
7  GHJ   Other   10

Ergo, lines CAN be repeated, and when they do, the value (C) stays the same for this line.

Using conditional formatting, (=$A2<>$A1), the repeated values (C-column only) are made white.

How can I do a SUM of these values, so that each distinct line will only be calculated once (Should return 45)?

In advance, thanks

PS: I'm using Excel 2003

Upvotes: 0

Views: 611

Answers (2)

Andrius Naruševičius
Andrius Naruševičius

Reputation: 8578

Does using the filter function work for you?

http://office.microsoft.com/en-us/excel-help/filter-for-unique-values-or-remove-duplicate-values-HP010073943.aspx

http://office.microsoft.com/en-us/excel-help/count-occurrences-of-values-or-unique-values-in-a-data-range-HP003056118.aspx

EDIT: I just solved it for you. Cheers!

https://docs.google.com/spreadsheet/ccc?key=0AsSiq9ugaybxdHk2MGIyVFRHbXN2LUYxdHRaTVM2cUE

Upvotes: 0

Marc
Marc

Reputation: 11613

When I put this in cell D9 and enter it as an array formula (Ctrl+Shift+Enter), it works for your scenario. But note that there's blank row in Row 8 for this to work:

=SUM(IF(B1:B7<>B2:B8,D1:D7,0))

Upvotes: 2

Related Questions