# Sum of the Max's for each subset

#### eggyjla

##### Active Member
Looking for a "oneliner: or CSE perhaps that sums the maximum values for each subset. See example below. The answer should be "9".
book1
ABCD
1X1
2X2
3X3
4Y1
5Y2
6Y3
7Z1
8Z2
9Z3
Sheet1

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
Also not every x,y,z will have (3) values or perhaps be sorted in any way. I've tried a couple combinations of =sum(max(if without luck. Is there perhaps another math term for this type of method.

Array enter

=MAX(IF(A2:A10="X",B2:B10))+MAX(IF(A2:A10="Y",B2:B10))+MAX(IF(A2:A10="Z",B2:B10))

Should provide the sum.

Please not that Dave's formula is a CSE formula. and must be entered with CTRL+SHIFT+ENTER
lenze

Hi Eggy

2 questions:

- does column A have always the same 3 strings or can it have an arbitrary number of different strings?

- in the case where there's more than one max value corresponding to the same string should it be added just once (in your example, if B2=3 then for the string "A" there would be 2 values equal to the max)?

Hi,

If you have more data or want an approach that will work regardless of the entries in column A, a database type approach might be of interest. Such as with a query table or pivot table: basically using SQL to do the work. Or even via VBA a query table, pivot table or ADO+recordset or similar. (If using a pivot table, the SQL could be simpler and just feed in the max values and let the pivot table do the summation.)

Assuming the data is on worksheet Sheet1 and has headers type and value in row 1 with data under, the SQL for a query table could be
Code:
``````SELECT Sum(MyMax) AS [MyTotal]
FROM
(SELECT type, Max(value) AS [MyMax]
FROM [Sheet1\$]
GROUP BY type)``````

To create a query table, save the file and then start via menu path data, import external data, new database query. Excel files, OK. Your Excel file & OK to any message about no visible ranges. Hit 'options' and 'system tables' to be able to see worksheet names. Or if you have used a non-dynamic named range for the source data you won't need that step. Pick the fields so that they appear in the little box on the RHS and continue until you get the option to edit in MS Query. Then hit the SQL button and replace the text you see by the text above. OK to enter that, & OK to any warning message if you get one. Then hit the 'open door' icon to return the data to an Excel worksheet. This is now a refreshable query table: it refreshes like a pivot table. This should be great even on tens or hundreds of thousands of data rows with data in closed or open Excel or database files.

I didn't test, but I'm pretty sure you can work without headers in your source data, the SQL would be slightly different. Untested,
Code:
``````SELECT Sum(MyMax) AS [MyTotal]
FROM
(SELECT F1, Sum(F2) AS [MyMax]
FROM [Sheet1\$]
GROUP BY F1)``````
where I've assumed column A [called field F1] has the identifiers and column B [F2] the values.

HTH, Fazza

A late thought: what about a pivot table?

Just set the data field to MAX instead of SUM. And then maybe =getpivotdata("value",a3)

HTH, F

Fazza-
A pivot table would be the approach normally taken. However I do not have just one table. In an effort not to clutter or expand the size of the wb, I was looking for a single CSE formula I could use. My wb has approx 30 sheets.

pgc -
Yes I was looking for a silution that would work regardless of values entered in C[A]. There aren't any strings to worry about or if there are multiple instances of MAX() for a given label in C[A]. Only one value should be returned for each value in C[A].

Hi again

Try in E1:

=SUM(IF(A1:A100<>"",IF(MATCH(A1:A100&B1:B100,A1:A100&B1:B100,0)=ROW(A1:A100)-ROW(A1)+1,IF(MMULT((A1:A100=TRANSPOSE(A1:A100))*(B1:B100<TRANSPOSE(B1:B100)),--(ROW(A1:A100)>0)),,B1:B100))))

Ex:

pgc-
Thanks for the work. I had no idea that it would have been so much!

Replies
0
Views
178
Replies
6
Views
729
Replies
14
Views
988
Replies
7
Views
281
Replies
4
Views
72

1,207,191
Messages
6,076,995
Members
446,248
Latest member
tim_crouse

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back