Min of SumProduct

ShosMeister · 10-19-2008, 05:23 PM

Can't seem to figure this one out.

I have dates in column A and values in column C. What I want, is to find the minimum value in C for each month of the year.

I have the SumProduct working properly and it does correctly count the number of times each month occurs, but, I can't seem to figure out how to get the minimum value. Like this:

Code:

Column A	Column C
01/20/2005	1947.50
02/20/2005	1947.50
03/21/2005	1981.00
04/20/2005	1981.00
05/19/2005	1122.00
06/20/2005	1122.00
07/20/2005	1096.50
08/20/2005	1096.50
09/19/2005	1034.00
10/20/2005	1034.00
11/17/2005	965.50
12/20/2005	965.50
01/19/2006	1800.00
02/20/2006	1800.00
03/21/2006	1575.50
04/20/2006	1575.50
05/19/2006	1188.00
06/20/2006	1188.00
07/21/2006	1293.50
08/20/2006	1293.50
09/20/2006	1339.50
10/20/2006	1339.50
11/20/2006	1369.50
12/20/2006	1369.50
01/18/2007	1527.00
02/20/2007	1527.00
03/23/2007	2309.50
04/20/2007	2309.50
05/21/2007	1053.00
06/20/2007	1053.00
07/25/2007	2641.00
08/20/2007	1142.00
09/19/2007	1318.00
10/18/2007	1302.00
11/23/2007	1465.00
12/19/2007	1581.00
01/22/2008	1702.00
02/20/2008	1779.00
03/26/2008	2536.00
04/21/2008	1286.00
05/19/2008	830.00

The SumProduct(Month(RANGE)=1) correctly comes up with 4 (there are 4 entries for January). What I need to do now is have it give me 1527 which is the minimum for all of the January's (it was in 2007).

I'd like to keep this to a formula rather than VB if possible. Any ideas?

ratcat73 · 10-20-2008, 01:55 AM

G'day

I don't have a sumproduct, but I have an array formula

Code:

=INDEX($A$1:$A$40,MATCH(MIN(IF(MONTH($A$1:$A$40)=   1,$C$1:$C$40)),$C$1:$C$40,0))

Must be entered with Ctrl+Shift+Enter to get { } around the formula.

And where I have the number one in blue in the code. Your welcome to put that has a cell reference if you wish to change the month your looking up.

Remember to adjust your range to suit your data range

HTH

ratcat73 · 10-20-2008, 03:04 AM

Hello again,

Sorry I got carried away.

I display in my first post is the formula that will return the date of the smallest amount

Code:

=INDEX($A$1:$A$40,MATCH(MIN(IF(MONTH($A$1:$A$40)=    1,$C$1:$C$40)),$C$1:$C$40,0))

This is the one that will return the amount

Code:

=MIN(IF(MONTH($A$1:$A$40)=    1,$C$1:$C$40))

Must be entered with Ctrl+Shift+Enter to get { } around the formula.

And where I have the number one in blue in the code. Your welcome to put that has a cell reference if you wish to change the month your looking up.

Remember to adjust your range to suit your data range

HTH[/quote]

ShosMeister · 10-20-2008, 05:57 AM

Rat, AWESOME!!! Works perfectly. I guess I was making it too difficult trying to use the SumProduct.

Cheers

Mate!

10-19-2008, 05:23 PM	#1 (permalink)
ShosMeister Registered User Join Date: Jan 2008 Location: Harpers Ferry, WV Posts: 400 OS: OS X 10.4.x	Min of SumProduct Can't seem to figure this one out. I have dates in column A and values in column C. What I want, is to find the minimum value in C for each month of the year. I have the SumProduct working properly and it does correctly count the number of times each month occurs, but, I can't seem to figure out how to get the minimum value. Like this: Code: Column A Column C 01/20/2005 1947.50 02/20/2005 1947.50 03/21/2005 1981.00 04/20/2005 1981.00 05/19/2005 1122.00 06/20/2005 1122.00 07/20/2005 1096.50 08/20/2005 1096.50 09/19/2005 1034.00 10/20/2005 1034.00 11/17/2005 965.50 12/20/2005 965.50 01/19/2006 1800.00 02/20/2006 1800.00 03/21/2006 1575.50 04/20/2006 1575.50 05/19/2006 1188.00 06/20/2006 1188.00 07/21/2006 1293.50 08/20/2006 1293.50 09/20/2006 1339.50 10/20/2006 1339.50 11/20/2006 1369.50 12/20/2006 1369.50 01/18/2007 1527.00 02/20/2007 1527.00 03/23/2007 2309.50 04/20/2007 2309.50 05/21/2007 1053.00 06/20/2007 1053.00 07/25/2007 2641.00 08/20/2007 1142.00 09/19/2007 1318.00 10/18/2007 1302.00 11/23/2007 1465.00 12/19/2007 1581.00 01/22/2008 1702.00 02/20/2008 1779.00 03/26/2008 2536.00 04/21/2008 1286.00 05/19/2008 830.00 The SumProduct(Month(RANGE)=1) correctly comes up with 4 (there are 4 entries for January). What I need to do now is have it give me 1527 which is the minimum for all of the January's (it was in 2007). I'd like to keep this to a formula rather than VB if possible. Any ideas?

10-20-2008, 01:55 AM	#2 (permalink)
ratcat73 Registered User Join Date: Oct 2008 Location: Australia Posts: 51 OS: Vista Home Basic	Re: Min of SumProduct G'day I don't have a sumproduct, but I have an array formula Code: =INDEX($A$1:$A$40,MATCH(MIN(IF(MONTH($A$1:$A$40)= 1,$C$1:$C$40)),$C$1:$C$40,0)) Must be entered with Ctrl+Shift+Enter to get { } around the formula. And where I have the number one in blue in the code. Your welcome to put that has a cell reference if you wish to change the month your looking up. Remember to adjust your range to suit your data range HTH

10-20-2008, 03:04 AM	#3 (permalink)
ratcat73 Registered User Join Date: Oct 2008 Location: Australia Posts: 51 OS: Vista Home Basic	Re: Min of SumProduct Hello again, Sorry I got carried away. I display in my first post is the formula that will return the date of the smallest amount Code: =INDEX($A$1:$A$40,MATCH(MIN(IF(MONTH($A$1:$A$40)= 1,$C$1:$C$40)),$C$1:$C$40,0)) This is the one that will return the amount Code: =MIN(IF(MONTH($A$1:$A$40)= 1,$C$1:$C$40)) Must be entered with Ctrl+Shift+Enter to get { } around the formula. And where I have the number one in blue in the code. Your welcome to put that has a cell reference if you wish to change the month your looking up. Remember to adjust your range to suit your data range HTH[/quote]

10-20-2008, 05:57 AM	#4 (permalink)
ShosMeister Registered User Join Date: Jan 2008 Location: Harpers Ferry, WV Posts: 400 OS: OS X 10.4.x	Re: Min of SumProduct Rat, AWESOME!!! Works perfectly. I guess I was making it too difficult trying to use the SumProduct. Cheers Mate!