How do I find the highest sum of multiple cells in column A where the sum is based on multiple cells matching in column B in Google Sheets?
I deeply apologize for the title being unclear. It is difficult to explain what I am looking for in such a small amount of characters. To provide some background, see the table below for reference.
| Column A | Column B |
|---|---|
| 600 | apple |
| -100 | apple |
| -200 | apple |
| 500 | orange |
| -100 | orange |
| 300 | pear |
I am looking for a single formula that will find the highest sum of values in column A that have matching values in column B. Per the table, apple has the highest individual value of 600 at A2. However, when summing apple's corresponding values in column A, 600 - 100 - 200 = 300. When performing the same calculations with orange, 500 - 100 = 400. Thus, I would like the formula to return 400 since it is greater than apple's 300.
At some point, I ended up with the following formula. After testing, I realize it is incorrect:
=SUM(FILTER(B2:B7,A2:A7=INDEX(A2:A7,MATCH(MAX(COUNTIF(A2:A7,A2:A7)),COUNTIF(A2:A7,A2:A7),0))))
It appears the above formula first finds the highest occurring fruit in column B and then sums it's corresponding values in column A afterwards. The solution I am looking for shouldn't care about the number of occurrences in column B.
Any help would be appreciated. Thanks in advance!
=index(query(A:B,"SELECT SUM(A), B WHERE NOT A IS NULL GROUP BY B ORDER BY SUM(A) DESC"),2)
Result:
Or
=index(sort(HSTACK(map(unique(B1:B6),lambda(a,sumif(B1:B6,a,A1:A6))),unique(B1:B6)),1,0),1)
- Downloading a PDF version of the open spreadsheet in Google Drive via scripts
- exporting spreadsheet to pdf then saving the file in google drive
- How to get last number in a row on Google Spreadsheets
- A Script to Simplify Creating a SO Table
- Archive a row after a dropdown status is selected on multiple sheets
- How to extract URL from Link in Google Sheets using a formula?
- Create list of dates in Google Sheets based on day-of-month schedule
- Get Google Sheet by ID?
- Return Column Based on Column Headers
- Add title row with ARRAYFORMULA in Google Sheets
- Why does my Google Service Account not see files shared with it, but can see files created with it?
- Google Sheets macro to copy a line of data to next empty row on another sheet
- Google Sheets - Auto border to a Dynamic Data
- Fix Google Sheets formula so it will append the header title of each column it is pulling data from, instead of just the first column's title
- VLOOKUP IMPORTRANGE to select the column number in another file also
- Split cell with dates and find matching dates on headers
- How to get the next Bi-weekly date based on a given start date and today's date in Google Sheets
- How can I determine if a respondent's email address is unique in a list?
- ARRAYFORMULA in GOOGLE SHEETS
- Can a google-apps-script be written to download a csv file from spreadsheet directly to a local hard drive with an onEdit trigger?
- API call to directory.members.insert failed with error: Resource Not Found: groupKey
- How to find common words in two columns in Google Sheets
- Get list of unique words without numbers and symbols from paragraph range in Google Sheet?
- Apps Script Delete Image
- App Script to download specific cells in Google Sheet to PDF
- Export Google Docs comments into Google Sheets, along with highlighted text?
- INDIRECT function in an INDEX-MATCH-MATCH
- How to write to a "Google Spreadsheet" from Excel 2003 VBA
- How to use date with LIKE inside query?
- Know if the cell is being used in any formula