max function with conditional statment

[SCC]

Is there an equivilant of sumif for finding the maximum value. Given the
data below I would like to find the peak value for each time of each day.
The highest number for Monday at 7, 8, etc.

Day 7 AM 8 9 10 11
Mon 5 10 23 44 35
Tue 1 9 18 34 60
Wed 9 25 29 48 50
Mon 9 12 14 16 45
Tue 5 28 30 52 55
Wed 9 25 32 62 77

 

[Glenn]

Is there an equivilant of sumif for finding the maximum value. Given the
data below I would like to find the peak value for each time of each day.
The highest number for Monday at 7, 8, etc.

Day 7 AM 8 9 10 11
Mon 5 10 23 44 35
Tue 1 9 18 34 60
Wed 9 25 29 48 50
Mon 9 12 14 16 45
Tue 5 28 30 52 55
Wed 9 25 32 62 77

With your data above in A1:F7, put "Mon" in A9, "Tue" in A10 and "Wed" in A11.
Then put the following array-entered (commit with CTRL+SHIFT+ENTER) formula in
B9 and copy down and across to F11:

=MAX(IF($A$2:$A$7=$A9,B$2:B$7,0))

 

[Mike H]

Hi,

Maybe

=MAX(IF(A2:A7="Mon",B2:F7))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correct then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

 

[T. Valko]

Try something like this...

A2:A7 = weekdays
B1:F1 = 7,8,9,10,11

Array entered** :

=MAX(IF($A2:$A7="Mon",B2:B7))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Copy across to get the max for the other hours.

 

[Bernard Liengme]

With your data entered starting in A1
In H1 I typed: Mon
In I1 to M1 I typed 7,8,9....
In I2 I used =SUM(MAX(IF($A$2:$A$7=$H$1,B2:B7))) and completed it with
CTRL+SHIFT+ENTER as it is an array formula
And copied across to M2
I get values 9, 25, 32, 62, 77
best wishes

 

[muddan madhu]

assumed data are in Col A : F

From range G1 to L4 put data like this

Day 7 8 9 10
Mon
Tue
Wed

in Cell H2 put this formula =MAX(IF($A$2:$A$10=$G2,B$2:B$10)) ( use
ctrl + shift + enter )

and drag it across and down.