# MAX function with conditions

#### wesimmo

##### Board Regular
I am trying to write a function that finds the maximum value in a range, where a condition is met in another range. The ranges are of equal size.

What i am trying to do is look along a row of week ending performance stats that span a year and find the maximum value for say january.

I have a row with the dates the weeks end on, and then a row that uses the MONTH function to determine the month that that week falls in.

3 rows below that i have a row of values.

so i attempted this, but it causes my Excel to lock and Dr Watson.

whereabouts would be the range that the month function is in, condition would be 1 for January, rowsdown would be 4.

Function findmax(whereabouts as Range, condition as single, rowsdown as single) as Long

Dim x as Long

x = 0

If Activecell.Value = condition Then

If Activecell(rowsdown, 1).Value > x Then

x = Activecell(rowsdown, 1).Value

End If

End If

Next C

findmax = x

End Function

But no luck, i'm thinking the Activecell is the problem, but what should i use?

Thanks

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
are you looking for a formula like this?

Code:
``{=MAX(IF(C1:C8=\$D\$1,B1:B8,0))}``

sorry for the example containing code in dutch
Map1
ABCDE
1dateresultdisplayresultformonthmaxresult
21-1-200512112455
31-2-2005212
43-1-20053211
514-1-20054321
630-1-20051231
75-2-20054552
825-3-2005343
914-5-2005215

sorry for the example containing code in dutch
second msgbox of htmlmaker procedure shows
"Please click [Yes], when you would like to use FormulaLocal for display Formula."
so click no and you will get it in english
also read the FAQ to avoid
(click "view code", select code, copy)

best regards,
Erik

Thanks, just what i needed, the wonderful world of array formulas eh?

Opens up a whole range of possibilities (and saves the coding). :wink:

But it has made me wonder how to do this with SUMPRODUCT.

i.e. perform sumproduct on two ranges where a third range equals a certain value, tried replicating the above

=SUMPRODUCT(IF('Volumetrics - Collections'!E1:N1=F1,'Volumetrics - Collections'!E15:N15,'Volumetrics - Collections'!E18:N18))

but that doesn't work.

try this
=SUMPRODUCT(--(\$E\$1:\$N\$1=\$F\$1),\$E\$15:\$N\$15,\$E\$18:\$N\$18)
=SUMPRODUCT(--('Volumetrics - Collections'!\$E\$1:\$N\$1=\$F\$1),'Volumetrics - Collections'!\$E\$15:\$N\$15,'Volumetrics - Collections'!\$E\$18:\$N\$18)
best regards,
Erik

Cheers that works, what does the "--" mean?

Learning all the time and i've been playing with excel for years, its what makes it such fun! :wink:

wesimmo said:
Cheers that works, what does the "--" mean?

Learning all the time and i've been playing with excel for years, its what makes it such fun! :wink:

The formula contains an equality test, with either TRUE or False as result. The -- bit coerces such a result into Excel's numerical equivalents, that is, TRUE into 1 and FALSE into 0.

the formula is multiplying three arrays, then summing up the results
Code:
``````row1  row15   row18
__________________
0   *   10  *   20
1   *   15  *   10
0   *   20  *   40
0   *   30  *   23
1   *   11  *   12
more rows
(0 means not equal to criterion)``````
result: 0 + 150 + 0 + 0 + 132 = 182
(since we are talking about rows the table should be transposed, but i thought it would be more clear displayed vertically)

Replies
1
Views
229
Replies
5
Views
240
Replies
2
Views
234
Replies
3
Views
1K
Replies
0
Views
235

1,203,070
Messages
6,053,363
Members
444,657
Latest member
jessejames1of3

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back