# Sum(if statement <>

#### peter8848

##### Board Regular
Hi,

I am doing a assay formula for sum(if statement from one workbook to another workbook so the value stays even though the source workbook is closed.

however it seems like all equal ones work but the <> does not work as I cannot put multiple unequal items and it seems like I can put one at the time of if<>, there are like 18 items I need to exclude so is it possible to fix it rather than nested if <>?

thanks,

Peter

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Hello Peter

You are a bit vague with your statement of "however it seems like all equal ones work but the <> does not work as I cannot put multiple unequal items and it seems like I can put one at the time of if<>, there are like 18 items I need to exclude so is it possible to fix it rather than nested if <>?" What exactly do you mean when you say, "<> does not work?" Are you saying it works, but is giving the wrong result? Or are you getting some kind of error message? If so, what is the error message?

There is no reason the '<>' won't work, however, if you are getting the wrong answer, try reversing the order of the two possible results that may be returned. Remember, by using the '<>', you are now looking at the negative (opposite) possibilities as opposed to using the '='.

One thing I noticed in the list of names, is that you need to insert a comma between the two quote marks for "MENTROL,","NELSON". Also make sure all names are spelled correctly. For example should 'DAVOD' be 'DAVID'?

TotallyConfused

Last edited:
Hello Peter

A quick addition to my post #2. In my last line where I said "One thing I noticed in the list of names, is that you need to insert a comma between the two quote marks for "MENTROL,","NELSON". You also need to remove your comma in "MENTROL,".

I hope this works for you.

TotallyConfused

Last edited:
You can do something like this:

=SUM(IF(Current!\$K:\$K=E35,IF(Current!\$F:\$F="NAME",IF(Current!\$E:\$E=\$D\$2,IF(Current!\$D:\$D=\$C\$2,IF(Current!\$C:\$C=\$B\$2,IF(ISERROR(MATCH(Current!\$H:\$H,{"ALLEN","BOB","CHARLIE","DAVOD","DANEIL","EDWARD","LAN CHOO","MARY","MENTROL","NELSON","NICK","PATRICK","TOM"},0)),Current!\$R:\$R,0)))))))

If the name matches something in your list, MATCH returns the position. If it doesn't find it in the list, MATCH returns an error. Then the ISERROR function detects that and calls it good.

It's a pretty slow function though. You would be well served to use row values if possible.

This non-array entered formula might work better:

=SUMIFS(Current!\$R:\$R,Current!\$K:\$K,E35,Current!\$F:\$F,"NAME",Current!\$E:\$E,\$D\$2,Current!\$D:\$D,\$C\$2,Current!\$C:\$C,\$B\$2)-SUM(SUMIFS(Current!\$R:\$R,Current!\$K:\$K,E35,Current!\$F:\$F,"NAME",Current!\$E:\$E,\$D\$2,Current!\$D:\$D,\$C\$2,Current!\$C:\$C,\$B\$2,Current!\$H:\$H,{"ALLEN","BOB","CHARLIE","DAVOD","DANEIL","EDWARD","LAN CHOO","MARY","MENTROL","NELSON","NICK","PATRICK","TOM"}))

In both cases, you'll need to add your external workbook name to the ranges. Full disclosure, I just tested these on a local workbook, but they should work.

Last edited:
Hi Eric,

I think your suggestion is working thanks a lot!

So by using F(ISERROR(MATCH(Current!\$H:\$H,{"ALLEN","BOB","CHARLIE","DAVOD","DANEIL","EDWARD","LAN CHOO","MARY","MENTROL","NELSON","NICK","PATRICK","TOM"} is it excluding this text as the critieria under Current!\$H:\$H?

Sorry it just in my head I could not see the login for this as I was trying to use <> function to work.

Cheers,

Peter

Hi TotallyConfused,

Yes I am trying to get the excel to look into the opposite as opposed to using "=", however, it looks like the excel only excludes the 1st two text from the long text "ALLEN","BOB","CHARLIE","DAVOD","DANEIL","EDWARD","LAN CHOO","MARY","MENTROL,""NELSON","NICK","PATRICK","TOM"}

Sorry I could have done some typo for coma or text as I did not use the original for my work confidentiality purposes I just manually type them out for examples.

Cheers,

Peter

Yes, any of those names will be excluded from the total. You really can't use a large list of < or > with an array formula, the resolution of the Boolean expression rarely matches what you want. Instead, you find a way to convert that large list into a single TRUE/FALSE. So the MATCH basically says "Is it in this list?" and the ISERROR answers FALSE, which is what you want.

Eric,

Thanks again, you are a legend! Sorry just wondering why " the resolution of the Boolean expression rarely matches what you want" but the way you used iserror and match is really smart!!!

Cheers,

Peter

Replies
1
Views
184
Replies
1
Views
235
Replies
0
Views
455
Replies
9
Views
284
Replies
8
Views
1K

1,216,487
Messages
6,130,943
Members
449,608
Latest member
jacobmudombe

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back