# 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  Register To Reply

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.  Register To Reply

3. ## Re: SUMIFS not equal

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

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")  Register To Reply

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  Register To Reply

6. ## Re: SUMIFS not equal

That did it!! Thanks  Register To Reply

7. ## Re: SUMIFS not equal

How do I reply with "Solved" ?  Register To Reply

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.   Register To Reply

9. ## Re: SUMIFS not equal

Sambo Kid
Thanks!!  Register To Reply

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