Forum Discussion
Help with SUMIF for good karma
Oh, my...where to begin? Not to mince words, but that workbook needs attention--a LOT of attention--paid to design before trying to make the "presenting formula" work. May I suggest you spend some time reading the attached paper--it's old and doesn't mention some of the newest features of Excel, but the basic concepts of design still apply. Of particular value would be documenting what each section is about...even for yourself if you're the only one who'll be using it.
SimoneC2220 messaged me with the following and another copy of the workbook. Here was her message:
@mathetes I appreciate your patience. I should have thought of that. I'm trying to do a few things. Datasheet is 2024. In the Mappings tab, I'm try to sum the cells with numeric values (2024 s2:ch255) based on multiple criteria. first is 2024, column h value, then columns s1:ch1 but instead of using individual columns when referencing values I need in 2024, I'd like to use a range in Mappings tab. For example, I would like sum of all cells in 2024 columns s1:ch255 where 2024 column h = "cd" and range s1:ch1 match Mappings tab b37:b50. Result to appear in Mappings e17.
To which I replied:
Simone: See the attached, cell Mappings E17. I've written a formula using SUM and FILTER. What I've realized, however, is that your last request -- range s1:ch1 match Mappings tab b37:b50 -- really means you do NOT want all of '2024'!S2:CH255 to be sourced in this; instead, you want only S2:AG255, because it's those column headings that match the headings in Mappings B37:B50.
Now to make this really easy, assuming there are meaningful "higher level" labels that can be applied to some of those sections, I'm naming ranges (see this reference) so that you can use a name in the formula rather than cell references. In this case, I'm calling S2:AG255 "Clients" -- seems from your sheet a reasonable name. And I named the range in 2024 column H, to be "JiraLead" based on the column heading. The resulting formula is: =SUM(FILTER(Clients,JiraLead=C17)) and that I copied down the rest of column E. There were several rows, however, where FILTER came up empty, which generated an error message, so the final formula reads
=IFERROR(SUM(FILTER(Clients,JiraLead=C17)),"No match")
which results in "No match" where the no cell in that range contains a number, so there's nothing for SUM to work on.
BUT the most useful information is the squad names on rows s2:ch2. I'm going to need to be able to run queries on that data itself. eg, how many items in a1:a255 did column h (business team) request for rows s2:ch2 (manufacturing teams)? And, that's where I am stuck in the redesign now. it's almost like rows s2:ch2 should be rows themselves with the items and their other data as columns but that itself will be unwieldly because the items list can be 400+ lines by itself. What do you think?
My expertise (to the extent I can even use that word) is more in transactional databases. [I was for several years in the 1990s the director of a database for HR and related systems in a major corporation.] From what you've written, it sounds like what you may be tasked with doing is tracking just that--transactions--in counting orders or requests of various products by various teams...when it occurs, quantity, etc. That could be a single table, with columns that use data validation to identify the team and the product, and yes, that list of teams could be a table by itself used as the basis for data validation.
Let me ask you to back up, away from the existing workbook itself, and describe the purpose it's supposed to serve in the organization. Or, if it's more than a single purpose, what is the primary purpose, and secondary or tertiary purposes.
In fact, the more I think about this, it's possible (I'm speculating) that a well-designed history of transactions--orders, who, what, when, how much--could be summarized by a Pivot Table without any formula at all. Familiarize yourself with the Pivot Table at this link (or through any of a myriad of YouTube videos). See if that concept fits your need.
To illustrate, I have a workbook that contains every check and every credit card transaction for the last five years -- date, payee/vendor, amount, primary budget category, secondary budget category -- and a single pivot table summarizes all that data, for any given year, by month and categories. No formulas needed. I'm attaching a simple example of that (not any real data, and far simpler than my actual).
mathetes You've been so helpful. I gave up and am just trying to cleanup the datasheet 2024. TBH, it didn't even have the mappings tab before so at least I have that defined. The basic issue I see and it's huge is on 2024, a1:a255 are unique names and to the right of that is all data pertaining to those names. is it part of this file, is it part of that file, what's it called here, there, etc. BUT the most useful information is the squad names on rows s2:ch2. I'm going to need to be able to run queries on that data itself. eg, how many items in a1:a255 did column h (business team) request for rows s2:ch2 (manufacturing teams)? And, that's where I am stuck in the redesign now. it's almost like rows s2:ch2 should be rows themselves with the items and their other data as columns but that itself will be unwieldly because the items list can be 400+ lines by itself. What do you think?
PS - I know no one here is paid for your kind services. Altho, if there were a way, I'd be happy to. I am very thankful that you're letting a total newb compared to your experience pepper you with ridiculous questions.
PPS - I'm in awe of Joe and how clearly he articulates the concepts. #goals
Here's a good video introduction to the FILTER function. (What you're up against, just from seeing the #SPILL! error) is a need to get a single answer to what is in the nature of things a dynamic array function. FILTER is one of a set of functions that were designed specifically as "dynamic array" functions. They can be nested in a SUM (or other, such as AVERAGE, MIN, MAX) function to produce a single summary number from what otherwise is an array.
ExcelJet.Net is an excellent source for explanations of any and every Excel function. I have it bookmarked for quick reference.
- My guess after looking at it briefly is that you'd probably benefit from learning the FILTER function, which, nested in a SUM function, might be able to achieve more efficiently what you're trying to do. But without understanding all of the various tables to which you refer, it's hard--I and others here are volunteers, not MSFT employees paid to do this--to make sense of the data and the various tables, and we'd need to have that understanding to being to grasp the purpose of the formula in the first place. That's precisely why a good design is so important (and, yes, I know you get that). It's a bummer to be handed a workbook like this created by somebody else and told to make it work.
Anyway, if you can put into words what this sum is supposed to reflect, where it's coming from, what the criteria are, and so forth, maybe I and others could make some educated guesses. The reason I ask that you put it into words--often one of the best ways to write a formula in the language of Excel is to first write it in normal conversational English. And especially if it's as multi-faceted as this one, that's important for explaining it to an outsider. Otherwise we (the outsiders in this case) first have to reverse engineer the formula that isn't working, create our own understanding, and then try to write a working formula, all with data that also are unfamiliar. - I get it. I have to redesign the whole thing. I was planning to anyway. Just don't have the time to do so before leaders need the data. TY for the guidelines. I'll use them well.
- Thanks. I'm picking up a file created by someone else and I'm definitely a newb. Reading the guide rn but still need a way to sum with multiple criteria using ranges for both lookup and return.