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

gberg

Board Regular
Joined
Jul 16, 2014
Messages
205
Office Version
  1. 365
Platform
  1. Windows
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 #AdjustCost
71D000175%-95%3,073,284
71D0001Final3,097,975
71D003275%-95%933,248
71D0032Final923,708
71D004475%-95%548,985
71D0044Final571,565
71D005175%-95%704,370
71D005675%-95%206,801
71D0050522,270
71D0057878,609
71D00581,301,725
71D00601,196,818
9,403,841
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
With Power Query

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"
 
Upvote 0
I've never used power query, guess I'll give it a try. Thanks for the advice
 
Upvote 0
Try:

Excel Formula:
=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)))
 
Upvote 0
Simplify

Excel Formula:
=LET(a,A2:A13,b,B2:B13,c,C2:C13,SUM(IF(COUNTIF(a,a)=1,c,IF(b="Final",c))))
 
Upvote 1
Check this and revert -

Book1
ABCD
1Job #Adjust Cost Include
271D000175%-95%30,73,284No
371D0001Final30,97,975Yes
471D003275%-95%9,33,248No
571D0032Final9,23,708Yes
671D004475%-95%5,48,985No
771D0044Final5,71,565Yes
871D005175%-95%7,04,370Yes
971D005675%-95%2,06,801Yes
1071D00505,22,270Yes
1171D00578,78,609Yes
1271D005813,01,725Yes
1371D006011,96,818Yes
141,39,59,35894,03,841
Sheet1
Cell Formulas
RangeFormula
D2:D13D2=IFS(B2="","Yes",AND(B2="Final",A2=A1),"Yes",AND(B2="75%-95%",A2<>A3,A3<>""),"Yes",TRUE,"No")
C14C14=SUM(C2:C13)
D14D14=SUMIFS(C2:C13,D2:D13,"Yes")
 
Upvote 0
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:


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

😇
 
Upvote 1
Solution

Forum statistics

Threads
1,223,246
Messages
6,170,999
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top