formula needed for sumifs

[jp16]

example, in col A, i have list of text. lets say A to Z (might repeat). col B, i have number. 1-100 (will repeat). in col C. i have dollar value. There are no blank. I will have a drop down list in D1 that shows A to Z. lets say i choose R. i want a formula in E1 that show the biggest number in col B if Col A is R. in F1, sum up all the number in col C that is R in col A and the largest number in Col B.

[pjwhitfield]

I'm not great at all on array formulas but the following should work

Please Login or Register  to view this content.

...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.

[jp16]

I'm not great at all on array formulas but the following should work

Please Login or Register  to view this content.

...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.

would it work as well if i use Large instead of Max?

[JeteMc]

It should.
The array entered formula* that uses the LARGE function would read: =SUMIFS(C:C,A:A,D1,B:B,LARGE(IF(A:A=D1,B:B),1))
Alternatively, since you are using the 2010 version, you should be able to use the following regular formula:

Please Login or Register  to view this content.

* activated as described in post #2.
Let us know if you have any questions.