Forum Discussion
Help with SUMIF for good karma
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
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).
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.