0

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

Improve this question
1
  • When you say you "can't change the layout," does that mean you can't add a column? Or a row? Where do you want the sum to show up? Commented May 7, 2012 at 13:42

2 Answers 2

Reset to default
2

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))
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Damn i feel dumb right now; I've tried that exact formula a dozen times, but forgot the Ctrl+Shift+Enter... Thank you for your swift reply
@GunnarKnudsen, I know that feeling! Glad it worked out for you.
You could avoid CSE if you want by using SUMPRODUCT, i.e. =SUMPRODUCT((A1:A7<>A2:A8)+0,C1:C7)
0

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

Improve this answer

3 Comments

No, sorry, The report needs to show all values, but only sum some of them... - However will give this a upvote... Marc as given a answer which works fine for my needs...
I am a bit confused now since I got 45 as an answer too :) And it does show all values.
Oh you meant about the first two links. Yeah, don't mind them then. The working solution is on third :P

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.