Solved

MS Excel 2010: SUMIFS Sorting Criteria & Exclusion Behavior

Posted on 2014-11-03
14
322 Views
Last Modified: 2015-02-08
I have an issue plaguing me with Excel that has to do with the =SUMIFS function. Although I have been able to include execute sorting by specific criteria to another worksheet against data on another worksheet, I cannot seem to figure out how to exclude data based on a specific marker in a column. Unfortunately, the data is proprietary so I cannot share it, but I will provide a clear explanation of the behavior I need and the basic structure of my workbook source. I am using the following below for a particular cell:

=SUMIFS('DATA(0&1)'!$M:$M,'DATA(0&1)'!$A:$A,"12",'DATA(0&1)'!$B:$B,"2009",'DATA(0&1)'!$C:$C,"01",'DATA(0&1)'!$E:$E,"00001")

Open in new window

To explain, the first column matched after the range is looking for the month of December or "12" numerically. The second is looking for the physical year. The third is looking for a parent account code, in this case "01". The last is matching an child account code, "00001".

The data comes from a proprietary system employed by the organization which generates an Excel report--I then copy/paste this report into my workbook and my scripted cells generate sum totals from the criteria. This is the first type of cell I use this function for. However, I have another worksheet that needs to access the same data, the same way, but exclude everything except a specific item. Between the physical year and the parent account code in the example above, there is one more additional piece of data that distinguishes what the information applies to. We'll call this a item code for the sake of simplicity, which suggests what the money was spent towards. This is where I come into a problem.

The data that needs to be excluded isn't marked with anything. The report generates puts a singular (or possibly plural) number of blank spaces into that searchable cell. Normally, an item code will have two alphanumeric digits, such as 'A1'. Initially I tried the following:

=SUMIFS('DATA(0&1)'!$M:$M,'DATA(0&1)'!$A:$A,"12",'DATA(0&1)'!$B:$B,"2009",'DATA(0&1)'!$F:$F," ",'DATA(0&1)'!$C:$C,"01",'DATA(0&1)'!$E:$E,"00001")

Open in new window


Unfortunately, that doesn't work. I can't seem to find a reference to identifying 'white space' in a cell when performing a function to match and sort.

I sure hope someone has run into this problem, because searching books and Google hasn't given me any clear leads, just a host of avenues that haven't panned out (could be my execution, always have much to learn). Thank you for any assistance you can provide!
0
Comment
Question by:jalongoria
14 Comments
 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
you can use sumproduct to be able to exclude data. please uploaded a dummy example file, so that put the working formula in there.
0
 

Author Comment

by:jalongoria
Comment Utility
@ProfessorJimJam

Thanks for the response. I've attached a file that shows the intent and irrelevant to actual expenses recorded--this is the best I can do without getting into specifics.

You can see how the sum manifests, but is incapable of splitting apart the Item Code (IC) 'blank' cell entry on "DATA(0&1)" from the "IA" and "1Q" IC amounts. I do not want to create a list of ICs to filter by because it would grow to astronomical proportions and I already have entries listed in the "DATA(0&1)" I can use to reduce bloat--rather, I would prefer to exclude the blank cells under "TOTAL(LIABILITY)" so I have only the items with actual two-digit ICs appearing.
EE-01-A.xlsx
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
Just restating your problem to make sure I understand:

You have a SUMIF function that you have used to isolate and exclude data based on criteria in four columns with values (A, B, C, E).  However, you have a fifth column (F) that you want to include in the SUMIF function and want to exclude values that have one or more blanks in them (ex., " ", "  "), rather than alphanumeric codes (ex., "A1").

My recommended solution involves two steps.
1) Highlight column F of your data and do a search and replace - changing all spaces to another character, for example a pipe character (|) - something that wouldn't normally occur in that column.
2) Then, change your SUMIF formula to this:
=SUMIFS('Data(0&1)'!$M:$M,'Data(0&1)'!$A:$A,"12",'Data(0&1)'!$B:$B,"2009",'Data(0&1)'!$F:$F,"|*",'Data(0&1)'!$C:$C,"01",'Data(0&1)'!$E:$E,"00001")
I've underlined the key change.

Of course, you can use any other symbol or character that is convenient except an asterisk (*).  

I've attached an example workbook.

Regards,
-Glenn
EE-Q28549527.xlsx
0
 

Author Comment

by:jalongoria
Comment Utility
Old, broken file, new one attached.
EE-01-A.xlsx
0
 

Author Comment

by:jalongoria
Comment Utility
@Glenn Ray

Thanks for the suggestion. I had considered this, however, I'm trying to build this product to be as plug and play as possible... meaning, I want myself and eventually my successors to just pull the data dump from source and copy/paste those values to the spreadsheet without having to change characters with a find/replace.

It's a great idea, but this was what I was initially doing several times a day which became a time-consuming operation. I would like to avoid this method and compensate for the spaces or double-spaces, if possible. Thank you!
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
I'm not a fan of "blanks as data", hence the suggestion for doing a search and replace.  However, if you didn't want to replace the spaces with characters, this formula also works:
=SUMIFS('Data(0&1)'!$M:$M,'Data(0&1)'!$A:$A,"12",'Data(0&1)'!$B:$B,"2009",'Data(0&1)'!$F:$F," *",'Data(0&1)'!$C:$C,"01",'Data(0&1)'!$E:$E,"00001")

Note the space before the asterisk.  This should pick up any value in column F that starts with a space.

Updated example workbook attached.

Regards,
-Glenn
EE-Q28549527.xlsx
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:jalongoria
Comment Utility
@Glenn Ray

Neither am I, but unfortunately, this data is also fed to another pre-existing product that requires those blank spaces. Overall, accepting the situation as it is with the formatting as it exists is the best, if less than ideal, solution.

Regarding your solution, true, it does function (and this is one I invoked previously based on cursory reading through several forum posts), but it doesn't exclude content, it includes it. From "TOTAL(LIABILITY)", I want to remove anything with spaces so that I get a accurate compilation of costs separate from those not including a two-digit IC.

I apologize if this wasn't somewhat clear before, thought I had clarified that.
0
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 500 total points
Comment Utility
I misunderstood you; I thought the entire SUMIF was your exclusionary function.  To get the total liability cost (column M) LESS those items with spaces in column F, you'd just subtract this from your first formula:
=SUMIFS('Data(0&1)'!$M:$M,'Data(0&1)'!$A:$A,"12",'Data(0&1)'!$B:$B,"2009",'Data(0&1)'!$C:$C,"01",'Data(0&1)'!$E:$E,"00001")-SUMIFS('Data(0&1)'!$M:$M,'Data(0&1)'!$A:$A,"12",'Data(0&1)'!$B:$B,"2009",'Data(0&1)'!$F:$F," *",'Data(0&1)'!$C:$C,"01",'Data(0&1)'!$E:$E,"00001")

Updated workbook attached (new data values changed to demonstrate).

-Glenn
EE-Q28549527.xlsx
0
 

Author Comment

by:jalongoria
Comment Utility
@Glenn Ray

I really appreciate the effort you're putting into this. Unfortunately, though this solution functions, it doesn't appear it will work. If I understand your solution posted correctly, the issue now is that the replication work to total each sum and subtract using a helper cell or worksheet would be enormous. Right now, there are approximately 27K rows I need to sum into specific categories, which would mean I would have to pull each out with an individual helper cell/worksheet per row to get the correct amount. I am trying to exclude those spaces to save on having to distribute the sum against additional calculations (i.e. subtracting to the net).

I need just one cell that can find the criteria, remove the unwanted portion from the data source and post the total in that category. The intent is to sum amounts by category (of which there are roughly 30 categories), which is what the IC denotes. This is done across multiple years and thousands of transactions. I am beginning to wonder if =SUMIFS is actually the proper function to be using since it appears to be geared more towards inclusive search criteria versus exclusion. It's quite powerful and I am glad I came across it, but it doesn't seem flexible/smart enough for this.

I am reading up on =SUMPRODUCT right now that ProfessorJimJam previously mentioned above to see if that has an exclusion field. Sorry this is such a pain, I have got to get it right the first time.
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
The SUMPRODUCT function works much slower than an equivalent SUMIFS function, so you may want to rethink that.

I'm still at a loss as to why my suggestion solution will not work for you.  You indicated that you want to exclude rows of data that have one or more spaces in column F.  The latest function does that:  it will subtract the SUMIF total of rows that have value that begins with a space in column F.  The example workbook has two rows that would be excluded, each with different numbers of spaces.

Your example file does not resemble the data layout you described in your original post.  If you could post a redacted file with just a few rows of relevant data (say 20-30) I'm sure we can find a reasonable solution for you.

Regards,
-Glenn
0
 

Author Comment

by:jalongoria
Comment Utility
@Glenn Ray

I will work on adding some more rows into the mix, but the data structure hasn't actually changed that would hinder function. I just removed columns from the mix that held irrelevant or proprietary data. The intent was to make it simpler to understand without unnecessary noise. The following is my updated function (which I believe you've already discovered in the cell itself):

=SUMIFS('DATA(0&1)'!$F:$F,'DATA(0&1)'!$A:$A,"1.00",'DATA(0&1)'!$B:$B,"2008",'DATA(0&1)'!$C:$C,"01",'DATA(0&1)'!$D:$D,"00001")

Open in new window


Your solution breaks off from my worksheet (the current standard by which this data is tracked by my organization), which causes me some confusion when processing the solution you've provided. My current =SUMIFS function searches for the defined criteria. You'll notice I have two worksheets besides the data set itself. Both worksheets use the same =SUMIFS function to find data. Using my last attached workbook, I could:

=SUMIFS('DATA(0&1)'!$F:$F,'DATA(0&1)'!$A:$A,"1.00",'DATA(0&1)'!$B:$B,"2008",'DATA(0&1)'!$E:$E," *",'DATA(0&1)'!$C:$C,"01",'DATA(0&1)'!$D:$D,"00001")

Open in new window



for column E, but that will return me results only for anything with a space or more in front of it--a solid solution, if I was looking to extract data into the first worksheet in a "closer view" of specific costs versus remove that data in the second worksheet which I want to account for all other ICs.

Simply (which may or may not be realistic), all I want to do is strip any data that has an IC with a space or double-spaces included in the cell from the rest of the data shown. I want to do this non-destructively by using a function to sort by with criteria versus destructively deleting those spaced IC rows (and/or running a parallel data worksheet in the process) like I have been doing. I don't see a need for the bloat when the data set is essentially the same--I just have to find a way to get both results.

The only other option I know of is to create an array or helper cells annotating specific ICs... however, those are constantly updating to new ICs, where the space/double-space data always remains the same and available. I want to avoid this solution at all costs because when I tried it before it became a real pain to keep up-to-date.
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
Comment Utility
The reason my solution "breaks" from your example sheet is that your original post shows a completely different data table set up on the "Data(0&1)" sheet than in your example.  My own example sheet follows the original layout - key being the value amounts to sum are in column M (not F) and the Item Code is in column F (not E).

Additionally, the formulas in the "TOTAL(LIABILITY)" sheet are incomplete, as they disgregard the IC value on the same row.  They should be changed to:
C4: =SUMIFS('DATA(0&1)'!$F:$F,'DATA(0&1)'!$A:$A,"1.00",'DATA(0&1)'!$B:$B,"2008",'DATA(0&1)'!$C:$C,"01",'DATA(0&1)'!$D:$D,"00001",'DATA(0&1)'!$E:$E,A4)
C5: =SUMIFS('DATA(0&1)'!$F:$F,'DATA(0&1)'!$A:$A,"1.00",'DATA(0&1)'!$B:$B,"2008",'DATA(0&1)'!$C:$C,"01",'DATA(0&1)'!$D:$D,"00001",'DATA(0&1)'!$E:$E,A5)

I see no need for any helper columns/cells; if you want to exclude any rows where there is a space or multiple spaces in the Item Code column (again, the original post states this is in column F), then my compound SUMIF function works.

-Glenn
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Not enough information to confirm an answer.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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 will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now