SUM using multiple IF criteria (and one criteria being only unique identifiers)

[craigp]

Please see attached example.

I am wanting to SUM the values (e.g. salary) in column B.
But only for unique values in column A (e.g. employee ID - there are duplicates).
And only if there is a value >0 in colums C, D or E (e.g. they have received an incentive for any of the 3 schemes).

I originally had a formula like this:
=(SUMIFS(Sheet1!B:B,Sheet1!C:C,">0")+SUMIFS(Sheet1!B:B,Sheet1!D:D,">0")+SUMIFS(Sheet1!B:B,Sheet1!E:E,">0"))

But realised there were duplicate values in column A (and therefore column B).

So I started with a formula like this:
={SUM(IF(FREQUENCY(IF(Sheet1!A2:A7<>"",MATCH(Sheet1!A2:A7,Sheet1!A2:A7,0)),ROW(Sheet1!B2:B7)-ROW(Sheet1!B2)+1),Sheet1!B2B7))}

However I now need to incorporate the criteria of IF values in columns C, D or E are >0. This is where I have become stuck..

Your help would be much appreciated.

Craig

 

[azumi]

Perhaps:

=SUM(IF(FREQUENCY(IF(A2:A7<>"",IF(OR(C2:C7>0,D2:D7>0,E2:E7>0),MATCH(A2:A7,A2:A7,0))),ROW(B2:B7)-ROW(B2)+1),B2:B7))

 

[Ananda Kumar C]

May be this:

=SUM(IF(FREQUENCY(IF(A2:A7<>"",MATCH((A2:A7)*((C2:C7+D2:D7+E2:E7)>0),(A2:A7)*((C2:C7+D2:D7+E2:E7)>0),0)),ROW(B2:B7)-ROW(B2)+1),(B2:B7)*((C2:C7+D2:D7+E2:E7)>0)))

Azumi's formula may not work when the first occurrence of an employee ID does not have any incentive. (Try removing incentive value of 10,000 from cell C2, expected result should be still 420,000)

PS: Edited to simplify further.

 

[Faseeh]

@craigp

..Add the following in column F, F2 downward:

=COUNTIF($A$2:A2,A2)

and try this:

=SUMPRODUCT((F2:F7=1)*(B2:E7))

 

[jeffreyweir]

My suggestion: Turn your raw data into a PivotTable, which gets rid of duplicates. Then use the simple formula in the attached file on that PivotTable.

=M2*(SUM(J2:L2)>0)

 

[Faseeh]

...Or perheps this one, since i got a cross check of the final result @jeffreyweir

=SUM(IFERROR((FREQUENCY(A2:A7,A2:A7)<>0)*(C2:E7>0)*(B2:B7),FALSE))

with CSE