Forum Discussion
Display Sum Values Based on Inches
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.
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!
14 Replies
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.
- 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.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.
