Sum(if statement <>

peter8848

Board Regular
Joined
Oct 7, 2018
Messages
97
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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

TotallyConfused

Board Regular
Joined
May 4, 2017
Messages
211
Office Version
  1. 365
Platform
  1. Windows
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:

TotallyConfused

Board Regular
Joined
May 4, 2017
Messages
211
Office Version
  1. 365
Platform
  1. Windows
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:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
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:

peter8848

Board Regular
Joined
Oct 7, 2018
Messages
97

ADVERTISEMENT

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

Board Regular
Joined
Oct 7, 2018
Messages
97
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
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. :cool:
 

peter8848

Board Regular
Joined
Oct 7, 2018
Messages
97
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!!!(y)

Cheers,

Peter
 

Watch MrExcel Video

Forum statistics

Threads
1,108,969
Messages
5,525,960
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top