SUIMIFS only unique values

[quiqueperez]

Hi,

I'm trying to add the values in column A based on certain criteria in other columns using a SUMIFS formula.
However, some of the rows can be classed as duplicates for my purpose and I need to exclude them.

IN the attached example, I need to add the length of the ducts in column A. However, some ducts are duplicated (duct A is listed twice) and they need to be added only once.

If I wanted to add the length of the ducts in region HH, I would be expecting 25+12=37m

Is there any SUMIFS formula I can use for this.

Something like SUMIFS(Column A, Region, HH, only when the values in column Duct are unique)

Column values to add	Duct	Cable	Region
25	Duct A	Cable 1	HH
25	Duct A	Cable 2	HH
12	Duct B	Cable 1	HH
8	Duct C	Cable 2	HV

<tbody>
</tbody>

Many thanks!
Enrique

 

[Weazel]

maybe...

Excel 2012

	A	B	C	D	E
1	Column values to add	Duct	Cable	Region
2	25	Duct A	Cable 1	HH	37
3	25	Duct A	Cable 2	HH
4	12	Duct B	Cable 1	HH
5	8	Duct C	Cable 2	HV

<tbody>
</tbody>

Sheet4​

Array Formulas Cell Formula E2 {=SUM(IF(FREQUENCY(IF((B2:B5<>"")*(D2:D5="HH"),MATCH(A2:A5,A2:A5,0)),ROW(A2:A5)-ROW(A2)+1),A2:A5))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>