SUMIFS not equal

1. SUMIFS not equal

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")

Sluggy

2. Re: SUMIFS not equal

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.

3. Re: SUMIFS not equal

Hi ORoos - Seemed to work, but Chicago had the same amount for Maintenance and Fuel.

4. Re: SUMIFS not equal

=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")

5. Re: SUMIFS not equal

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

6. Re: SUMIFS not equal

That did it!! Thanks

7. Re: SUMIFS not equal

How do I reply with "Solved" ?

8. Re: SUMIFS not equal

to mark as solved go to the thread tools dropdown at the top of this post and it is in that list.

9. Re: SUMIFS not equal

Sambo Kid
Thanks!!

There are currently 1 users browsing this thread. (0 members and 1 guests)