max of sumifs of multiple columns

[peledan]

Hi All,
I'm new here, so sorry for any newb mistakes...

I'm trying to find the max between sumifs of multiple adjacent columns in a range, meaning something like this:
=max(sumifs(A1:A5, criteriaRange1, criteria1...), sumifs(B1:B5, criteriaRange1, criteria1...), sumifs(C1:C5, criteriaRange1, criteria1...), etc.)
But obviously without the commas, as I have a lot of columns to sum over.
I cannot use a helper line, as I have many possible changing criteria.
I tried to do it with an array function, and googled it for hours, but without success.
I was hoping for something like:
{=max(sumifs(A1:C5, criteriaRange1, criteria1...)}
But this obviously doesn't work as it just sums over the entire range and not per-column.

Any help would be appreciated!

 

[Dave Patton]

Excel Workbook
	A	B	C	D	E	F
1	1	4	50	20	20	A
2	2	5	55	30	20	B
3	3	6	60	40	20	B
4	4	7	65	55	20	B
5	5	8	70	60	20	C
6
7	B
8	180	180
3aa

Excel 2003

Cell Formulas
Range	Formula
A8	=MAX(SUMIF(F1:F5,A7,A1:A5),SUMIF(F1:F5,A7,B1:B5),SUMIF(F1:F5,A7,C1:C5),SUMIF(F1:F5,A7,D1:D5),SUMIF(F1:F5,A7,E1:E5))
B8	=MAX(SUMIF(F1:F5,A7,OFFSET(A1:E5,,{0,1,2,3,4})))

N.B.

This is a quick guess.

If you provide a concise example, someone may be able to provide additional help.

Dave

 

[peledan]

First of all thanks for the help,
I still cannot expand your solution to what I need (whatever I try doesn't work),
So I will post my full problem as you suggested.
Similarly to your example, this is what I need:
[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F
[/TH]
[/TR]
[TR]
[TH]1[/TH]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]AA
[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]AA
[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]BB
[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]BB
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]BB
[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What I need is the maximum sum of columns A-D, for which Col E = AA and col F = (A or B), for example.
In this case it is 105 from Col C.

3aa[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[/TR]
</thead><tbody>[TR]
[TH]1[/TH]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]20[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]20[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]20[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]20[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD="align: right"]180[/TD]
[TD="align: right"]180[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Excel 2003

Cell	Formula
A8	=MAX(SUMIF(F1:F5,A7,A1:A5),SUMIF(F1:F5,A7,B1:B5),SUMIF(F1:F5,A7,C1:C5),SUMIF(F1:F5,A7,D1:D5),SUMIF(F1:F5,A7,E1:E5))
B8	=MAX(SUMIF(F1:F5,A7,OFFSET(A1:E5,,{0,1,2,3,4})))

<tbody>
[TD="bgcolor: #FFFFFF"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<thead>[TR]

</thead><tbody>
</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]



N.B.

This is a quick guess.

If you provide a concise example, someone may be able to provide additional help.

Dave

 

[Andrew Poulsom]

Try:

=MAX(SUMPRODUCT(--(E1:E5="AA"),((F1:F5="A")+(F1:F5="B")),A1:A5),SUMPRODUCT(--(E1:E5="AA"),((F1:F5="A")+(F1:F5="B")),B1:B5),SUMPRODUCT(--(E1:E5="AA"),((F1:F5="A")+(F1:F5="B")),C1:C5),SUMPRODUCT(--(E1:E5="AA"),((F1:F5="A")+(F1:F5="B")),D1:D5))

 

[Dave Patton]

Excel Workbook
	A	B	C	D	E	F
1	1	4	50	20	AA	A
2	2	5	55	30	AA	B
3	3	6	60	40	BB	B
4	4	7	65	55	BB	B
5	5	8	70	60	BB	C
6
7	AA	A	B
8					105	105
9
3aaa

Excel 2003

Cell Formulas
Range	Formula
E8	=SUMPRODUCT(C1:C5,--(E1:E5=A7),--((F1:F5=B7)+(F1:F5=C7)))
F8	=MAX(SUMPRODUCT(A1:A5,--(E1:E5=A7),--((F1:F5=B7)+(F1:F5=C7))),SUMPRODUCT(B1:B5,--(E1:E5=A7),--((F1:F5=B7)+(F1:F5=C7))),SUMPRODUCT(C1:C5,--(E1:E5=A7),--((F1:F5=B7)+(F1:F5=C7))),SUMPRODUCT(D1:D5,--(E1:E5=A7),--((F1:F5=B7)+(F1:F5=C7))))

This gives the answer but it is a bit long.

Dave

 

[barry houdini]

Try this version

=MAX(MMULT(SUMIFS(OFFSET(A1:A5,0,{0;1;2;3}),E1:E5,"AA",F1:F5,{"A","B"}),{1;1}))

This works specifically when you have a single criterion in one column but two in another - {0;1;2;3} dictates the number of sum columns - change as required

 

[peledan]

Thanks for all the replies!
Andrew and Dave, your solutions are great, but not quite what I need, as their length is pretty proportional to the number of columns I'm summing over, which was the main original problem.

Barry, first of all, wow. This is such an elegant solution, and exactly the kind of thing I needed.
It works great, but if you have a min, I'd like to ask a thing or two about it, as I'm here to learn
1) What's the difference between inserting an array separated with commas and semicolons?
2) Why doesn't it need to be entered as an array function?
3) Can I somehow replace the {0;1;2;3} with a function returning this, such as the way COLUMN(A1:A4) as an array function returns {1,2,3,4}?

Thanks!

 

[barry houdini]

By having one set of values which is a "column" and one which is a "row" then the overall result is a matrix which is as long as the column and as wide as the row. You can see how it works by typing this in a cell

=A1:A4

now select the cell and press F9 and you see the values in those cells separated by semi-colons, e.g. if all values in the range were 3s then you'd see

{3;3;3;3}

...so by using this part

OFFSET(A1:A5,0,{0;1;2;3})

that returns an array of each of the four columns, but acting as a column of values....and by having the last criterion comma-separated like {"A","B"} then the result of the SUMIFS function is a 4 row x 2 column matrix, where the first column is the result for "A" and the second column is the result for "B", so in your example the result is

{1,2;4,5;50,55;20,30}

...but you need to combine the "A" and "B" values for each row.....and MMULT can do that

=MMULT({1,2;4,5;50,55;20,30},{1;1}) = {3;9;105;50}

and then MAX just gives you the MAX, i.e. 105

Typically you don't have to array enter if you use "array constants" like {0;1;2;3}, as in my example, but if you use a calculation which generates that array then it will need CTRL+SHIFT+ENTER.

So this version will do the same (array entered)

=MAX(MMULT(SUMIFS(OFFSET(A1:A5,0,ROW(INDIRECT("1:4"))-1),E1:E5,"AA",F1:F5,{"A","B"}),{1;1}))

with this part

=ROW(INDIRECT("1:4"))-1

generating the required {0;1;2;3}

so for 26 columns you could use

=ROW(INDIRECT("1:26"))-1

or you can specifically use the range like

=TRANSPOSE(COLUMN(A1:Z1)-1)

[you need TRANSPOSE because we need to have a column of values not a row]

 

[peledan]

Fabulous answer Barry!
I've learned so many things from it that I didn't even know existed in excel...
Thanks!