Sum(if statement <>

[peter8848]

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.

I need to have it =SUM(IF('[Source spreadsheet.xlsx]Current'!$K:$K=E35,IF('[Source spreadsheet.xlsx]Current'!$F:$F="NAME",IF('[Source spreadsheet.xlsx]Current'!$E:$E=$D$2,IF('[Source spreadsheet.xlsx]Current'!$D:$D=$C$2,IF('[Source spreadsheet.xlsx]Current'!$C:$C=$B$2,IF('[Source spreadsheet.xlsx]Current'!$H:$H<>{"ALLEN","BOB","CHARLIE","DAVOD","DANEIL","EDWARD","LAN CHOO","MARY","MENTROL,""NELSON","NICK","PATRICK","TOM"},'[Source spreadsheet.xlsx]Current'!$R:$R,0))))))),

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

 

[TotallyConfused]

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

 

[TotallyConfused]

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

 

[Eric W]

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.

 

[peter8848]

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

 

[peter8848]

Hi TotallyConfused,

Sorry about this.

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.

Thanks for your help.

Cheers,

Peter

 

[Eric W]

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.

Glad it helps.

 

[peter8848]

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