Excel Excel - How to Sum when Value Occurs at least once

[srevart]

I am new to this site, but the posts I have read seem helpful. Here's an issue that is driving crazy.

I am attempting to sum a column based on the occurrence of a value at least one time in 4 columns, but I don't want to compound the result.

Sample data.

client1 Client2 Client3 Client4 Revenue
a a c d 100
b a c d 200
b b a a 300

I would want to sum the revenue column such that when I lookup client a, the result value is 600 b/c it appears at least once on each row. The fact that client a appears in multiple columns on 2 of the rows does not mean that the revenue column for those rows should be counted twice. I can't seem to figure it out. Sumproduct seems to get me close, but not quite.

 

[SlimJim]

It would seem that nobody wants to have a go at this so here goes. Its a bit crude and I have tried to do it so that it can easily be adapted to more rows and columns.

Make an extra column, F, at the right end. In the first row put a (or b or c or d as you wish)
In the second row put the formula

F2=COUNTIF(A2:D2,$F$1)

Now copy paste this formula down the F column

F3=COUNTIF(A3:D3,$F$1)
F4=COUNTIF(A4:D4,$F$1)

In the first blank row put the formula

 F5=SUMIF(F2:F4,">0",E2:E4)

The content of F5 gives the answer. If you change the content of F1 to b then you get the answer for b etc. If you want all answers visible at the same time then construct a new column for each of a, b, c, d

 

[srevart]

Thanks for your attempt. That's a good trick. What I neglected to mention is that I actually have about 1000 clients which means using your trick I would have to have the ability to go out 1000 columns. Ultimately, I want to produce a result that shows:

client sales
a 600
b 600
c 300
d 300
about 1000 clients would be listed

That being said, I actually do have an application for your method where I have a similar situation with a smaller list of values. I have 8 markets, each of which could be referenced in 4 columns per row, but I only want to add the sales for that row once if the market is referenced in one or more of the columns. I could modify your method such that each column represents a market.

Thanks

 

[SlimJim]

I had a feeling the example might be somewhat simplified, hardly any need for automation with such small numbers.

The only thing that springs to mind, which I am not capable of doing (yet), would be to put in the one extra column and write a VBA loop attached to a button which runs down your list of clients taking the client reference putting in the top of the new column, reading the value at the bottom and dropping into place alongside the client reference. Of course since I don't know how to do it, it may well be that it is not possible.