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.
I'm not great at all on array formulas but the following should work
...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.
=SUMIFS(C:C,A:A,D1,B:B,MAX(IF(A:A=D1,B:B)))
If someone has helped you then please add to their Reputation
would it work as well if i use Large instead of Max?Originally Posted by pjwhitfield
I'm not great at all on array formulas but the following should work
...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.=SUMIFS(C:C,A:A,D1,B:B,MAX(IF(A:A=D1,B:B)))
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:Formula:
=SUMIFS(C:C,A:A,D1,B:B,AGGREGATE(14,6,(B:B)/(A:A=D1),1))
* activated as described in post #2.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks