Nested Sumif's or how to sum data based on nested criteria

**dlietz** · 10-07-2014, 10:41 AM

Hello,

I'm new to the forum but this looks like a great resource for excel questions. Here is my puzzle...

Included	type	value
y	i	15
n	c	5
y	i	20
y	e	6
y	e	7
y	c	9
n	i	6
y	a	10

I want a bottom row to sum the 'value' column where the 'included' column is 'y' and then the highest value for each unique letter in 'type'. So in this example the answer would be 20 (for the 'i' value) + 9 (for the 'c' value) + 10 (for the 'a' value) + 7 (for the 'e' value) = 46.

Any assistance is greatly appreciated.

**AlKey** · 10-07-2014, 10:53 AM

Maybe this

=MAX(IF(($A$2:$A$9="y")*($B$2:$B$9=E2),$C$2:$C$9))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

	A	B	C	D	E	F
1						Result
2	y	i	15		a	10
3	n	c	5		c	9
4	y	i	20		e	7
5	y	e	6		i	20
6	y	e	7
7	y	c	9
8	n	i	6
9	y	a	10

**ben_hensel** · 10-07-2014, 11:02 AM

The hardest part of this logic is the "max per type" inclusion. Trying to come up with a general solution by creating an intermediate array of unique values that could drive a loop in VBA function....

Well, first I want to ask, what are the constraints on that "type" column? Is there a maxium of ten possible "types" or 100? What are the constraints for what the string could be; is it always a single lowercase letter or what?

**dlietz** · 10-07-2014, 11:10 AM

Alkey,

I can work with this, I think. I'll have to do some more testing to make sure.

Thanks much.

**dlietz** · 10-07-2014, 11:13 AM

Ben,

The value for type will consist of 10 or less different values, it will always be lower case letters and no more than two characters. The solution that Alkey posted, while it does require me to add separate columns for the totals of each type, seems to work. If you think there may be a way to do it without adding the extra columns I would appreciate your input.

Thanks.

**ben_hensel** · 10-07-2014, 11:16 AM

Honestly if you're fine with an intermediate table, that's probably best: this whole "SUM of maxes per type" operation is unusual, so just putting it at the bottom like a total would be deceptive. Running out a table of MAX values, and then summing up those, would be immediately clear.

**dlietz** · 10-07-2014, 11:18 AM

I think that makes sense as well, thanks for your input.

**Speshul** · 10-07-2014, 11:36 AM

I've got the formula to pull the largest value per letter done if someone wants to take over and try and solve this without a helper column.

I'm out the door and don't have time to finish it, but I think this gets you about halfway there.

It pulls the largest value after sorting the list alphabetically, so use a lookup/index match to get the first value...idk. I can't think anymore.

CSE
=MAX(IF(($A$2:$A$9="y")*($B$2:$B$9=CHAR(SMALL(CODE($B$2:$B$9),ROWS($B$2:B2)))),$C$2:$C$9))