Finding Max value for range of cells meeting a critera

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is a bit hard to explain, but I am trying to find a formula where I can find the maximum value of a range of cells that meet a criteria. e

A
A
B
C
A 1
C
C 1

So I want to be able to find the highest value for the A’s, B’s and C’s. Therefore A would be 11, B 5 and C 2

Sorry if that doesn’t make sense. It’s hard to describe.
 
Hi Caroline,

Actually, it makes perfect sense! :-)

Try,

=MAX(IF(A1:A7="A",B1:B7))

which is to be entered using CTRL+SHIFT+ENTER.

Hope this helps!
 
Hi

With your data in range p.e. A2:A8, for criteria "A"
=MAX(($A$2:$A$8="A")*($B$2:$B$8))
entered as array formula (Ctrl+Alt+Enter - when properly entered, then the
formula is displayed in formula bar closed into curly brackets).


--
Arvi Laanemets
(When sending e-mail, use address arvil<At>tarkon.ee)



Caroline said:
This is a bit hard to explain, but I am trying to find a formula where I
Click to expand...
can find the maximum value of a range of cells that meet a criteria. eg
A 1
A 2
B 5
C 2
A 11
C 2
C 1

So I want to be able to find the highest value for the A’s, B’s and C’s.
Click to expand...
Therefore A would be 11, B 5 and C 2.
 
Instead of a formula, use a Pivot table. Put the letters in the Row
field and numbers in the Data field, summarized by Max.

Here's an example:

ftp://ftp.mcgimpsey.com/excel/caroline_demo.xls
 
Additionally you can create a new column with the
idividual data labels A, B and C.
You can do this using the Data>Filter>Advanced Filter.
Leave the Criteria Range empty and don't forget to check
the "Unique Records Only".
Then you have to change the mentioned formula to
=MAX(IF($A$1:$A$7=D1,$B$1:$B$7)) (imagine that the Data
Labels are in the cells A1:A7 and the values are in the
cells B1:B7, and you have have placed the unique data
labels to cells D1:D3) in cell E1.
Last you have to fill this formula to cells D2 and D3.

Hope I helped you
E.G
-----Original Message-----
Hi Caroline,

Actually, it makes perfect sense! :-)

Try,

=MAX(IF(A1:A7="A",B1:B7))

which is to be entered using CTRL+SHIFT+ENTER.

Hope this helps!
Click to expand...
 
Thank you all so much, especially JE McGimpsey for the help with Pivot tables. I've never been able to understand them so that's been especially helpful to :)

Thank you all again, you have no idea how much work you have saved me!
 
Thank you all so much, especially JE Mcgimpsey for the help with Pivot tables. I've never been able to understand them so that's been especially helpful to :)

Thank you all again, you have no idea how much work you have saved me!
 
Back
Top