Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 12
Default Looking for the equivalent of a Maxif function

Does anyone know of a good way to find largest value in a column B, if and
only if it satisfies the criteria in column A? Much like a sumif or countif.
For example, I have spreadsheet that lists all of my stores by market and by
region, within those sheets. I am trying to find the largest value for each
particular region. So I need the formula to look in column A for all
instances where the region is equal to region 1, and then find the largest
number in cloumn b that corresponds to all instances of region 1. I know I
can do this by sorting my sheet by region, and locking down my ranges
manually, but I run this file monthly and I have stores that open and close
without my knowledge. Any thoughts?
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 287
Default Looking for the equivalent of a Maxif function

Try

=MAX(IF(A1:A100="Region 1",B1:B100))

confirmed with CTRL+SHIFT+ENTER

"PerplexedinKY" wrote:

Does anyone know of a good way to find largest value in a column B, if and
only if it satisfies the criteria in column A? Much like a sumif or countif.
For example, I have spreadsheet that lists all of my stores by market and by
region, within those sheets. I am trying to find the largest value for each
particular region. So I need the formula to look in column A for all
instances where the region is equal to region 1, and then find the largest
number in cloumn b that corresponds to all instances of region 1. I know I
can do this by sorting my sheet by region, and locking down my ranges
manually, but I run this file monthly and I have stores that open and close
without my knowledge. Any thoughts?

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 54
Default Looking for the equivalent of a Maxif function

I would sugust using Pivot Tables. They are well worth learning.
--
CCO


"PerplexedinKY" wrote:

Does anyone know of a good way to find largest value in a column B, if and
only if it satisfies the criteria in column A? Much like a sumif or countif.
For example, I have spreadsheet that lists all of my stores by market and by
region, within those sheets. I am trying to find the largest value for each
particular region. So I need the formula to look in column A for all
instances where the region is equal to region 1, and then find the largest
number in cloumn b that corresponds to all instances of region 1. I know I
can do this by sorting my sheet by region, and locking down my ranges
manually, but I run this file monthly and I have stores that open and close
without my knowledge. Any thoughts?

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,624
Default Looking for the equivalent of a Maxif function

one way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=MAX(IF(A1:A1000="Region 1"),A1:A1000))

In article ,
PerplexedinKY wrote:

Does anyone know of a good way to find largest value in a column B, if and
only if it satisfies the criteria in column A? Much like a sumif or countif.
For example, I have spreadsheet that lists all of my stores by market and by
region, within those sheets. I am trying to find the largest value for each
particular region. So I need the formula to look in column A for all
instances where the region is equal to region 1, and then find the largest
number in cloumn b that corresponds to all instances of region 1. I know I
can do this by sorting my sheet by region, and locking down my ranges
manually, but I run this file monthly and I have stores that open and close
without my knowledge. Any thoughts?

  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,624
Default Looking for the equivalent of a Maxif function

Oops, should have been

=MAX(IF(A1:A1000="Region 1",B1:B1000))

In article ,
JE McGimpsey wrote:

=MAX(IF(A1:A1000="Region 1"),A1:A1000))

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,718
Default Looking for the equivalent of a Maxif function

=SUMPRODUCT(MAX((A1:A100="Region 1")*(B1:B100)))


"PerplexedinKY" wrote:

Does anyone know of a good way to find largest value in a column B, if and
only if it satisfies the criteria in column A? Much like a sumif or countif.
For example, I have spreadsheet that lists all of my stores by market and by
region, within those sheets. I am trying to find the largest value for each
particular region. So I need the formula to look in column A for all
instances where the region is equal to region 1, and then find the largest
number in cloumn b that corresponds to all instances of region 1. I know I
can do this by sorting my sheet by region, and locking down my ranges
manually, but I run this file monthly and I have stores that open and close
without my knowledge. Any thoughts?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
List of functions contained in the add-ins, esp. Analysis Toolpak Neil Goldwasser Excel Worksheet Functions 3 January 12th 07 01:43 PM
How do I know which cell a function is called from? Murami Excel Worksheet Functions 3 November 24th 06 03:22 PM
VBA Monty Excel Worksheet Functions 2 January 30th 06 02:37 PM
Date & Time mully New Users to Excel 4 May 23rd 05 11:56 AM
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. Robert AS Excel Worksheet Functions 4 December 2nd 04 11:49 AM


All times are GMT +1. The time now is 12:22 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"