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

SCC wrote:
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))

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

"SCC" wrote:

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

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.

--
Biff
Microsoft Excel MVP


"SCC" wrote in message
...
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 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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"SCC" wrote in message
...
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

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.



On Dec 3, 12:26*am, SCC wrote:
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