Solved

ARRAY IN SUMIFS WITH "<>" CRITERIA

Posted on 2014-04-29
4
822 Views
Last Modified: 2014-04-30
I have a SUMIFS formula where I would to like to have one of the criteria be an array of values that a particular range cannot be equal to.  I undertsnad how to do this when I only want to include an array of values, but I am having trouble doing the opposite.

=+SUMIFS(data!$F:$F,data!$C:$C,$A35,data!$D:$D,$D35,data!$G:$G,$B35,data!$I:$I,"<>{0068999,1000000,3300305}",data!$B:$B,"<="""&MONTH(F$27))

**Please note the values in the array are text, not numbers.

I can make it work with a SUMPRODUCT formula, but I wanted to see if this was possible as well.

Thanks for any help!
0
Comment
Question by:mak345
4 Comments
 
LVL 23

Expert Comment

by:NBVC
ID: 40029438
Unfortunately with SUMIFS you can't use the grouping method for "AND" checks (i.e. check that column is not equal to criteria1 AND column is not equal to criteria2, etc.... it can be used for checking "OR" checks.

You will need 3 separate SUMIFS added together (one for each separate column I check.  

Also, SUMIFS will convert Text string numbers to numbers and will look for those in column I.  So it will look for the number 68999 and not find it amongst numbers/text like 0006899

Sumproduct would be the better choice for that situation.  You will just need to try to limit the ranges or use Dynamic Named Ranges.
0
 
LVL 32

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 40029447
How about combining two SUMIFS?

One which includes everything, ie no criteria for Data!$I:$I, and one which includes only those items that you want to exclude. Then one less the other will give the result you want.

Thanks
Rob H
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 40030803
I don't think you need 3 separate SUMIFS, you can simply use 3 separate conditions in a single SUMIFS, e.g.

=SUMIFS(data!$F:$F,data!$C:$C,$A35,data!$D:$D,$D35,data!$G:$G,$B35,data!$I:$I,"<>0068999",data!$I:$I,"<>1000000",data!$I:$I,"<>3300305",data!$B:$B,"<="&MONTH(F$27))

If you want to exclude a longer list of values then Rob's method may suit better

regards, barry
0
 

Author Closing Comment

by:mak345
ID: 40032441
This turned out to be the best option.  When I attempted the SUMIFS formula with multiple "<>" criteria it ended up including all values.  SUMPRODUCT works, but I prefer the use of SUMIFS.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question