Sum values but exclude if there are duplicate values in a different columns

[gberg]

If there are duplicate values in "Column A" I only want to sum the cost where Column B has "Final"
In the example below I only want to sum the Cost values that are in yellow text

Job #	Adjust	Cost
71D0001	75%-95%	3,073,284
71D0001	Final	3,097,975
71D0032	75%-95%	933,248
71D0032	Final	923,708
71D0044	75%-95%	548,985
71D0044	Final	571,565
71D0051	75%-95%	704,370
71D0056	75%-95%	206,801
71D0050		522,270
71D0057		878,609
71D0058		1,301,725
71D0060		1,196,818
		9,403,841

 

[alansidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Adjust] <> "75%-95%")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Cost", Currency.Type}}),
    #"Calculated Sum" = List.Sum(#"Changed Type"[Cost]),
    #"Converted to Table" = #table(1, {{#"Calculated Sum"}})
in
    #"Converted to Table"

 

[gberg]

I've never used power query, guess I'll give it a try. Thanks for the advice

 

[DanteAmor]

Try:

=LET(a,A2:A13,b,B2:B13,c,C2:C13,SUM(IF(COUNTIF(a,UNIQUE(a))>1,FILTER(c,b="Final")))+SUM(IF(COUNTIF(a,a)=1,c)))

 

[DanteAmor]

Simplify

=LET(a,A2:A13,b,B2:B13,c,C2:C13,SUM(IF(COUNTIF(a,a)=1,c,IF(b="Final",c))))

 

[SanjayGMusafir]

Check this and revert -

Book1
	A	B	C	D
1	Job #	Adjust	Cost	Include
2	71D0001	75%-95%	30,73,284	No
3	71D0001	Final	30,97,975	Yes
4	71D0032	75%-95%	9,33,248	No
5	71D0032	Final	9,23,708	Yes
6	71D0044	75%-95%	5,48,985	No
7	71D0044	Final	5,71,565	Yes
8	71D0051	75%-95%	7,04,370	Yes
9	71D0056	75%-95%	2,06,801	Yes
10	71D0050		5,22,270	Yes
11	71D0057		8,78,609	Yes
12	71D0058		13,01,725	Yes
13	71D0060		11,96,818	Yes
14			1,39,59,358	94,03,841
Sheet1

Cell Formulas
Range	Formula
D2:D13	D2	=IFS(B2="","Yes",AND(B2="Final",A2=A1),"Yes",AND(B2="75%-95%",A2<>A3,A3<>""),"Yes",TRUE,"No")
C14	C14	=SUM(C2:C13)
D14	D14	=SUMIFS(C2:C13,D2:D13,"Yes")

 

[gberg]

That works, thanks!

 

[SanjayGMusafir]

That works, thanks!

Thanks for the feedback. With Excel 365 in you hand , I would still recommend to try @DanteAmor 's solution in #5. It's fantastic.

 

[DanteAmor]

With Excel 365 in you hand , I would still recommend to try @DanteAmor 's solution in #5. It's fantastic.

Thanks @SanjayGMusafir for the comment, maybe @gberg didn't see post #5, you just have to put the formula in cell C14:

=LET(a,A2:A13,b,B2:B13,c,C2:C13,SUM(IF(COUNTIF(a,a)=1,c,IF(b="Final",c))))

 

[gberg]

Dante, I like that better. Saves, nice that it does not use a helper column.