Sum column using NOT equal to criteria

[danhendo888]

https://1drv.ms/x/s!AvjBsEPEq12ngUm0YmMHEk1HGIPY?e=npLKjO

My data has account numbers and their corresponding $ value
Trying to add the $ value for those accounts which are not in the list in Column E
I.e. add column B except the items highlighted in grey

How best would you go about it?

(My formula in E15 doesn't seem to work)

 

[sandy666]

is that what you want?

with Power Query aka Get&Transform

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]GL account[/td][td=bgcolor:#5B9BD5] Total [/td][td][/td][td=bgcolor:#5B9BD5]Criteria[/td][td][/td][td=bgcolor:#70AD47]STotal[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9D9D9]

60701000​

[/td][td=bgcolor:#D9D9D9]

3,626.30​

[/td][td][/td][td=bgcolor:#DDEBF7]

60701000​

[/td][td][/td][td=bgcolor:#E2EFDA]

-1230425.17​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9D9D9]

60801000​

[/td][td=bgcolor:#D9D9D9]

1,332.17​

[/td][td][/td][td]

60801000​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9D9D9]

60901000​

[/td][td=bgcolor:#D9D9D9]

110,013.93​

[/td][td][/td][td=bgcolor:#DDEBF7]

60901000​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

61752000​

[/td][td]

(107,053.63)​

[/td][td][/td][td]

63109100​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

61753000​

[/td][td=bgcolor:#DDEBF7]

(6,693.94)​

[/td][td][/td][td=bgcolor:#DDEBF7]

63309000​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

61755000​

[/td][td]

(202.11)​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

61756500​

[/td][td=bgcolor:#DDEBF7]

(1,635.20)​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

61781200​

[/td][td]

(115.96)​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

61782000​

[/td][td=bgcolor:#DDEBF7]

(50,817.26)​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

61783000​

[/td][td]

(23,062.81)​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

61821100​

[/td][td=bgcolor:#DDEBF7]

(299,471.81)​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

63101000​

[/td][td]

-​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

63101300​

[/td][td=bgcolor:#DDEBF7]

-​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

63103100​

[/td][td]

(4,801.24)​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

63104000​

[/td][td=bgcolor:#DDEBF7]

(6,501.22)​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

63104100​

[/td][td]

(59.07)​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

63108000​

[/td][td=bgcolor:#DDEBF7]

(66,549.00)​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9D9D9]

63109100​

[/td][td=bgcolor:#D9D9D9]

(3,005.93)​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

63301000​

[/td][td=bgcolor:#DDEBF7]

(202,558.79)​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9D9D9]

63309000​

[/td][td=bgcolor:#D9D9D9]

(102,428.08)​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

67501217​

[/td][td=bgcolor:#DDEBF7]

(191.25)​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

67606900​

[/td][td]

(207,612.68)​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

67909110​

[/td][td=bgcolor:#DDEBF7]

-​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

67909900​

[/td][td]

(7,113.92)​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

68101910​

[/td][td=bgcolor:#DDEBF7]

(2.57)​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

69097001​

[/td][td]

(245,982.71)​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]// Result
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TypeTbl1 = Table.TransformColumnTypes(Source,{{"GL account", Int64.Type}, {"Total", type number}}),
    Criteria = Excel.CurrentWorkbook(){[Name="Criteria"]}[Content],
    TypeCrt = Table.TransformColumnTypes(Criteria,{{"Criteria", Int64.Type}}),
    Result = Table.NestedJoin(Criteria,{"Criteria"},TypeTbl1,{"GL account"},"Table1",JoinKind.RightAnti),
    Expand = Table.ExpandTableColumn(Result, "Table1", {"GL account", "Total"}, {"GL account", "Total"}),
    #"Grouped Rows" = Table.Group(Expand, {"Criteria"}, {{"STotal", each List.Sum([Total]), type number}}),
    RC = Table.RemoveColumns(#"Grouped Rows",{"Criteria"})
in
    RC[/SIZE]

this is NOT a vba

 

[Jonmo1]

Try this

=SUM(B5:B30)-SUMPRODUCT(SUMIF(A5:A30,E7:E11,B5:B30))

 

[joeu2004]

[.... deleted by me .... I like Jonmo1's formula better ]

 

[Peter_SSs]

.. or in a similar fashion add the ones you want directly

=SUMPRODUCT(--ISNA(MATCH(A5:A30,E7:E11,0)),B5:B30)