CODEWITHSUNDEEP
excelsumunique
Tavi Truman
Tavi Truman

Reputation: 107

Sum Unique/Duplicate Rows Only Once

I want to perform this SUM function in every row if the value in G is unique; if the value is a duplicate then fill the cell with $0

Here is my SUM formula : 

=(SUM('Base Calculator'!$K11:$Y11)*'Base Calculator'!$I11)+'Base Calculator'!$J11)

The values in Column "G" aren't sorted. The value is Column "G" is Text - e.g., "D01"

This is as far as I have gotten with experimentation: 

=IFNA($G11=INDEX(Fact1, MATCH('Base Calculator'!$G11,UNIQUE(Fact1,0,TRUE),0),1)+1,(SUM('Base Calculator'!$K11:$Y11)*'Base Calculator'!$I11)+'Base Calculator'!$J11)

*Fact1 is named range of Column G.

Sample data:

enter image description here

enter image description here

Thanks for your time and attention.

Upvotes: 0

Views: 856

Answers (1)

basic
basic

Reputation: 11968

The following formula can be used to sum up with condition of uniqueness:

=(SUM(A1:F1)*I1+J1)*--(COUNTIF($G$1:$G$10,G1)=1)

enter image description here

EDIT

If I understand correctly, you need sum duplicate values too, but only once. Then a small change to the existing formula will do that:

=(SUM(A1:F1)*I1+J1)*--(COUNTIF($G$1:G1,G1)=1)

enter image description here

Upvotes: 1

Related Questions