I'm looking for a formula similar to a SumIf, only I'm looking to return the mode for all cells matching the specified criteria, can anyone help? For instance, for zip code 11011, column B has the values 5, 5, 5 and 4. I'd like the formula to return "5" for this zip code. Thanks!
Jill
Last edited by jillteresa; 08-04-2010 at 06:08 PM.
If COL A is zip codes
If COL B is values to evaluate
If E1 holds the zip code to assess
Put this array formula in F1:
=MODE(IF($A$1:$A$1000=E1, $B$1:$B$1000, ""))
...and confirm by pressing CTRL-SHIFT-ENTER to activate the array.
Note the row designation in red, only set that down as far as needed, do not try to use an entire column.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Thank you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks