SUMIFS multiple OR AND criteria

[Aquarock]

I have half of my SUMIF working (Column E - simple SUMIFS equal two account codes), but that I am somehow missing how to sum all the remaining account codes (I've tried using a does not equal "<>" but its not working correctly) shown in Column N and adding them up to place in Column F? Excel example sheet attached for reference.

Any help is greatly appreciated as I know its something easy I am overlooking!

[Pepe Le Mokko]

Perhaps try a Pivot Table?

[Aquarock]

Moving data away from Pivot tables as we are dealing with mass amounts of data (thousands of lines to look up against each month)....I just included a tiny little example.

[Pepe Le Mokko]

IF PT is an option, then with large sheets PowerPivot would be a possibility or Power Query ?

[63falcondude]

Try these:

E7 =SUM(SUMIFS(M$7:M$25,L$7:L$25,D7,N$7:N$25,{"154500","649000"}))
F7 =SUMIFS($M$7:$M$25,$L$7:$L$25,D7,$N$7:$N$25,"<>154500",$N$7:$N$25,"<>649000")

You can also use this in F7:
=SUMIF(L$7:L$25,D7,M$7:M$25)-E7

[davsth]

pivottables would be better as less formulas and probably quicker
E7 you can lose the " " as the account number is a number
=SUMIFS($M$7:$M$25,$L$7:$L$25,$D7,$N$7:$N$25,154500)+SUMIFS($M$7:$M$25,$L$7:$L$25,$D7,$N$7:$N$25,649000)

but f6 easily
=SUMIFS($M$7:$M$25,$L$7:$L$25,$D7)-E7

[CARACALLA]

E7=IF($D7<>"",SUMPRODUCT(SUMIFS($M$7:$M$25,$L$7:$L$25,$D7,$N$7:$N$25,$N$13:$N$14)),"") copy down


F7=IF($D7<>"",SUMPRODUCT(SUMIFS($M$7:$M$25,$L$7:$L$25,$D7,$N$7:$N$25,"<>"&$N$13:$N$14)),"") copy down

[Aquarock]

Thank you ALL these work! Yes, I know pivots work in many cases, but the mass amount of data across many dynamic inputs just wasn't the way to handle all this for long term repetitive processes.

[63falcondude]

Glad we could help. Thanks for the rep!