Good Morning Everyone -
Have multiple codes I need to sum, except for 2. These are 5541 and 5542. On the Expense tab, Col D, I have this formula, but it clearly does'nt work. Ex: On the Expense tab, Boston has $77.85 for fuel, which is correct, but Maintenance should be all codes that don't equal 5541 & 5542 for Boston. Should be $146.85, its showing $371.55???
=SUMIFS(NetCost,Branch,$C2,Code,"<>5541")+SUMIFS(NetCost,Branch,$C2,Code,"<>5542")
Thanks in advance!
Sluggy
Hi Sluggy,
In D2 try: =SUMIFS(NetCost,Branch,Expense!$C2,Code,"<>5541",Code,"<>5542")
Your formula in the first part only excludes 5541, which includes 5542, the second half does the reverse, then add both. You need to exclude both in the same formula.
If your Question is answered; please mark it SOLVED. If you are happy with a member's solution, say 'Thanks' and click the 'Star' to Add Reputation.
Hi ORoos - Seemed to work, but Chicago had the same amount for Maintenance and Fuel.
Please try at D2
=SUMIFS(NetCost,Branch,C2,Code,"<>5541",Code,"<>5542",Code,"<>5541",Code,"<>5542")
if you change Transactions B4 code to Number then just below formula will work
=SUMIFS(NetCost,Branch,C2,Code,"<>5541",Code,"<>5542")
Expense
D2=SUMPRODUCT((Branch=Expense!C2)*(ISNA(MATCH(Code,Transactions!$B$3:$B$4,0)))*(NetCost))
Copy down
E2=SUMPRODUCT(SUMIFS(NetCost,Code,Transactions!$B$3:$B$4,Branch,Expense!C2))
Copy down
Last edited by CARACALLA; 06-25-2020 at 11:21 AM.
That did it!! Thanks
How do I reply with "Solved" ?
to mark as solved go to the thread tools dropdown at the top of this post and it is in that list.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Sambo Kid
Thanks!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks