The below formula isn't working and I think it's because the last argument is a range. Can anyone help me find a solution that will work please? =SUM(('2024'!$S$2:$CH$255)*(--('2024'!$H$2:$H$255=Mappings!$C19)))*(--('2024'!$S$2:$CH$2=Mappings!$B$37:$B$50)) Sample data file - https://docs.google.com/spreadsheets/d/18YO5ctgJeCn_hX4S1St7VkKfOEWxwUY6/edit?gid=1003849218#gid=1003849218

SimoneC2220 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 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).

It's kinda hard to help you without seeing what the formula is working with (i.e., seeing the cells to which it refers). Your hypothesis is a reasonable one. Why don't you proceed by testing that hypothesis? i.e., by making the last argument something other than a range. But you'd help us help you by posting a copy of the spreadsheet itself, not just the formula that somehow is failing to work as desired.

It would be better if you share a sample here in post/comment via one-drive or google-drive so that other contributor can also put formula for you.

Please edit share permission so that we can view it. Now the sheet is private and can't view or edit.

Help with SUMIF for good karma | Microsoft Community Hub

16 Replies

KhaledDardiri
Copper Contributor
Sep 27, 2024
This post had me intrigued I had to download the file to know if really a redesign is required, and me too I have to recommend a redesign. A well-planned design is halfway to victory. This file looks like when someone is still not sure how to begin or arrange the flow of data.
Sure, at least some columns need to cut down here. This large number of columns screams out loud that something is going wrong here. The best way to do that, if not too many rows will have data in every single column of those, is to make a column (like "Request Type") which will have a different value for each team (in A2:A255). If one team requests more types, add more rows.
I also advise you to use tables which will retain a proper formatting and make formulae more understandable.
You may direct message me if you need more help.
- mathetes
  Gold Contributor
  Sep 27, 2024
  KhaledDardiri wrote A well-planned design is halfway to victory.
  
  One might even say it's 3/4 of the way. Certainly without a well-planned design, the result will be challenging to maintain, even if it "works"
  
  This file looks like when someone is still not sure how to begin or arrange the flow of data.
  
  The file has all the markings of a process of tracking which was manually maintained on large ledger sheets, with the thought that one could just transfer that same format over to Excel. An understandable point of view, perhaps, but one that almost by definition fails to take advantage of Excel's remarkable abilities to manipulate data, slicing and dicing, extracting and arraying -- Excel's abilities to do "the heavy lifting". A manual process transferred directly to Excel still requires the user to find the box into which to put a check mark or date or quantity. Whereas listing the transactions which are consist of date, who, what, where, which, how much....is a sufficient database that Pivot Tables or some of the dynamic array functions can then process and summarize in those same "boxes".
mathetes
Gold Contributor
Sep 27, 2024
It's kinda hard to help you without seeing what the formula is working with (i.e., seeing the cells to which it refers). Your hypothesis is a reasonable one. Why don't you proceed by testing that hypothesis? i.e., by making the last argument something other than a range. But you'd help us help you by posting a copy of the spreadsheet itself, not just the formula that somehow is failing to work as desired.
- SimoneC2220
  Copper Contributor
  Sep 27, 2024
  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.
  
  File sent via Messages.
  - Harun24HR
    Bronze Contributor
    Sep 27, 2024
    It would be better if you share a sample here in post/comment via one-drive or google-drive so that other contributor can also put formula for you.

Forum Discussion

Help with SUMIF for good karma

16 Replies

Resources