Forum Discussion
Display Sum Values Based on Inches
In that case, the SUMIFS should still work. Converting the data to Tables one would have
= SUMIFS(Table1[MCHUPS], Table1[Off],[@Name])BTW I agree with mathetes support for updating Excel to 2021 or 365. Once you have it, the only impediment to writing great solutions is your past experience of spreadsheets!
I wish there was a way to write an English sentence and have Excel turn it into the formula I am seeking.
That'll probably come along in the next few months. After all, there is an AI program now that can write your college application essay. What you're asking can't be too far behind.
But for now, why don't you (seriously) give it a try--write in an English sentence what you are trying to accomplish. By trying to express it in incomplete "Excel language" you actually have made it harder to be certain what you're saying.
And spell out (as part of that English sentence) describe what's in
- Sheet1$A:$A (are we talking of the sum of numbers in that column? or a count of numbers? something else)
- Sheet1!$Z:$Z (ditto)
- Sheet1!$D:$D (ditto)
And whatever all those things are you appear to want the sum of the values in $D:$D to be placed in Sheet2!, cell $B2, if the various conditions are met.
What is to happen if the conditions are NOT met? i.e., the "else" part of the IF...THEN...ELSE
Additionally, one of the best things you could do, to help us help you, would be to post a copy of the spreadsheet (or a mockup if the real thing contains confidential or private info) on OneDrive or GoogleDrive, and then paste a link here that grants edit access.
When a name in sheet1 column A matches a name in sheet 2 column A, and the value in sheet1 column Z is between 70 and 74 inclusive, then display the sum of all the matches in sheet1 column D.
Otherwise, display "No match"
Sheet1 column A is alpha
Sheet 1 column D is numeric
Sheet 1 column Z is numeric
Sheet 2 column A is alpha
I am sorry but I am unable to upload an example spreadsheet. Thank you.
From your description the function to consider is SUMIFS. The function takes criteria values as text such as ">70" or ">"&70 held in the argument that follows the field to which it applies, but the documentation should make that clear.
Following up on the input you received from my friend, PeterBartholomew1 , here's a link to some helpful documentation on SUMIFS.
That said, there remains some ambiguity/confusion in your descriptions of what you want, and for your sake in attempting to use SUMIFS (or any other function), you will need to be clear in your own mind. For example, your first post says
IF Sheet2!$A3 = Sheet1!$A:$A
whereas your more recent text says
When [IF] a name in sheet1 column A matches a name in sheet 2 column A
...leaving a question (at least in my mind): is the formula to match a single cell in sheet 2 (cell A3 as the first description suggests) or however many matches exist between the entirety of both columns in both of the two sheets? The former is far easier and in many ways makes more sense (although admittedly I'm only viewing this in the abstract, not knowing what the whole of these two sheets are about)
And then, your description of the bottom line or desired conclusion of the function reads as
then display the sum of all the matches in sheet1 column D.
leaving an open question -- What matches? I.e., the only matches we've been asked to look for are between the columns A in the two sheets; do you mean the sum of the numbers in D for the rows that correspond to the matches in the column A of sheet1? Something else?
Let me return to my concluding paragraph of my first response. I offered an alternative to posting your actual spreadsheet...a mockup.
A mockup--from which all identifiable data has been removed--that would (a) show what kind of data occupies these sheets, how it's arrayed, etc., and (b) as an actual Excel workbook, would enable us ( PeterBartholomew1 or me or one of the other Excel gurus in this forum, most far more qualified than I am) to do some actual writing of formulas with the data arrayed as you have it.
A mockup can be created fairly readily, if you need further help, by just eliminating the actual names (and any other identifiable data), substituting names of Star Wars or Disney characters... enough to actually make it a representative sample size.......
- I have created a mock spreadsheet and posted it to my Google drive (https://drive.google.com/drive/folders/16l-QZ9gmLnXmX5dkDgmQ7ku_rzg2hbZ5?usp=sharing), but do not know how to make it available to you.
Let me try to explain more clearly.
I am looking for an array formula and I want the formula to be stored in Sheet2!B3. The formula is to sum however many matches exist between the entirety column A in both sheets. Yes, I want to sum the numbers in Sheet1 column D that correspond to the matches between Sheet!1 column A and Sheet 2 column A as long as the values in Sheet1 column Z are between 70 and 74.
To summarize, as best I can describe, the formula is to do the following: When a name in sheet1 column A matches a name in sheet 2 column A, and the values in sheet1 column Z are between 70 and 74 inclusive, then display the sum of all the matches in sheet1 column D.
Otherwise, display "No match"
Sheet1 column A is alpha
Sheet 1 column D is numeric
Sheet 1 column Z is numeric
Sheet 2 column A is alpha
Thanks again for your help