Display Sum Values Based on Inches

[rbellotti]

Need a formula for the following array:

SUM IF Sheet2!$A3 = Sheet1!$A:$A

         AND if Sheet1!$Z:$Z is more than or equal to 70 OR less than or equal to 74

         then return the sum of the values in Sheet1!$D:$D in Sheet2!$B2

I wish there was a way to write an English sentence and have Excel turn it into the formula I am seeking. Thank you.

[#2]

@rbellotti 

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.

[#3]

Thanks for the advice. Here is my English version of what I am trying to do.

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.

[#4]

@rbellotti 

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.

[#5]

@rbellotti 

Following up on the input you received from my friend, @Peter Bartholomew , 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 ( @Peter Bartholomew  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.......

[#6]

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

[#7]

@rbellotti 

When you have the Google sheet open, up at the top, on the right, you should see a "Share" button (image below).

Using it should give you a URL that you can paste here. If you can please make it one that grants us "edit" capability, rather than "View only"

[#8]

https://docs.google.com/spreadsheets/d/1AQLP6tuM_UniKqhfjYQGvhxWBXI4XOJ8/edit?usp=sharing&ouid=11414...

[#9]

@rbellotti 

Is this the kind of result you're expecting? Notice the formula up on the top row. That gets copied down to the rest of column B. If that's the kind of result you're looking for, then a similar formula would work for the Pts100M column.

Do you realize you left the first sheet, with all names, in this workbook? Don't think it's a big issue, since it appears they're all public figures anyway, and this isn't really "personal" information. But if you did that unintentionally, you might want to modify the shared sheet.

[#10]

Yes, these are the results I was expecting. What is the formula you used? Thank you.

[#11]

read more carefully the first line in my reply: the formula is on the top row of the image

[#12]

The spreadsheet tells me that the FILTER function isn't valid, so the formula doesn't work. I have double-checked that I typed it in correctly.

[#13]

@rbellotti 

What that tells me is that you have an older version of Excel. The FILTER function requires Excel 2021 or newer. I would recommend you update. It's worth it. That's not the only new feature, and especially if you're doing array manipulations, you'll find a number of very powerful functions, FILTER being only one.

Here, to tempt you, a video that formed my own introduction to FILTER, UNIQUE and SORT.....  These functions have given a whole new lease on life.

https://www.youtube.com/watch?v=9I9DtFOVPIg

[#14 · Accepted solution]

@rbellotti 

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!

[#15]

Thank you both. I appreciate your help.