Does anyone have any suggestions on how to use sumproduct function?
I would like to determine the min and max numbers under column B, if and
only if cell A2 = 1-Aug-2009 and cell C2 = "C".
Does anyone have any suggestions on how to do it in excel?
Thanks in advance for any suggestions
Eric

Re-word my questions

Does anyone have any suggestions on how to use sumproduct function?
I would like to determine the min and max numbers under column B, if and
only if any cell under column A = 1-Aug-2009 and under column C = "C".
Does anyone have any suggestions on how to do it in excel?
Thanks in advance for any suggestions
Eric

=MAX(IF(A1:A100=Date(2009,8,1),IF(C1:C100="C",B1:B 100))

=MIN(IF(A1:A100=Date(2009,8,1),IF(C1:C100="C",B1:B 100))

Confirmed with CTR:+SHIFT+ENTER not just ENTER


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=123911

Use these array* formulae:

=MAX(IF((A1:A100=--"2009-08-01")*(C1:C100="C"),B1:B100))

=MIN(IF((A1:A100=--"2009-08-01")*(C1:C100="C"),B1:B100))

Adjust the ranges to suit, but you can't use full columns (unless you
have XL2007). It's better to put the criteria in cells, so that you
don't have to amend the formula to change the criteria. So, if you put
the date in D1, for example, and the letter C in E1, then the formula
could become:

=MAX(IF((A1:A100=D1)*(C1:C100=E1),B1:B100))

=MIN(IF((A1:A100=D1)*(C1:C100=E1),B1:B100))

* An array formula should be committed using the key combination of
Ctrl-Shift-Enter (CSE) rather than the usual Enter. If you do this
correctly then Excel will wrap curly braces { } around the formula
when viewed in the formula bar - do not type these yourself. If you
edit the formula yo uwill need to use CSE again.

Hope this helps.

Pete

On Aug 10, 3:07*pm, Eric wrote:
Re-word my questions

Does anyone have any suggestions on how to use sumproduct function?
I would like to determine the min and max numbers under column B, if and
only if any cell under column A *= 1-Aug-2009 and under column C = "C".
Does anyone have any suggestions on how to do it in excel?
Thanks in advance for any suggestions
Eric