The lowest of all?

[misterdtc]

Dear all,

I have a list of items and each item can apperar several times
and I would like to sum all the amounts for the same item with the lowest year (formatted as number) and 0 otherwise

I can add all the result together with the sumif function but I am stuck with the condition of the lowest year for an item.

Any idea?

E.g.

Item Year Amount Result
a 2000 100 1225
a 2001 125 0
a 2002 150 0
a 2003 175 0
a 2004 200 0
a 2005 225 0
a 2006 250 0
b 2000 200 1435
b 2001 210 0
b 2002 205 0
b 2003 203 0
b 2004 201 0
b 2005 206 0
b 2006 210 0


Thank you in advance

Nico

 

[Claus Busch]

Hi Nico,

Am Wed, 29 Jan 2014 07:18:54 -0800 (PST) schrieb (e-mail address removed):

Item Year Amount Result
a 2000 100 1225
a 2001 125 0
a 2002 150 0
a 2003 175 0
a 2004 200 0
a 2005 225 0
a 2006 250 0
b 2000 200 1435
b 2001 210 0
b 2002 205 0
b 2003 203 0
b 2004 201 0
b 2005 206 0
b 2006 210 0

in D2 try:
=IF(B2=MIN($B$2:$B$100),SUMIF($A$2:$A$100,A2,$C$2:$C$100),0)
and copy down


Regards
Claus B.

 

[misterdtc]

Dear Claus,

thank you for your swift answer,

Your formula is working but only if the two items hacve the same lowest date for example if we use this configuration it is not working?

Any clue and tx for the support

a 2000 125 1235
a 2001 250 0
a 2002 320 0
a 2003 170 0
a 2004 180 0
a 2005 190 0
b 2001 200 0
b 2002 210 0
b 2003 205 0
b 2004 220 0
b 2005 250 0
b 2006 270 0

 

[Claus Busch]

Hi,

Am Wed, 29 Jan 2014 08:36:06 -0800 (PST) schrieb (e-mail address removed):

Your formula is working but only if the two items hacve the same lowest date for example if we use this configuration it is not working?

did you copy & paste or did you write the formula?
Have a look here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "SumIf"


Regards
Claus B.

 

[misterdtc]

Tx but with the workbook provided I have the same results if I change the starting date of the product b from 200 to 1999, see below
Item Year Amount Result
a 2000 100 0
a 2001 125 0
a 2002 150 0
a 2003 175 0
a 2004 200 0
a 2005 225 0
a 2006 250 0
b 1999 200 1435
b 2000 210 0
b 2001 205 0
b 2002 203 0
b 2003 201 0
b 2004 206 0
b 2005 210 0
c 2000 100 0
c 2001 250 0
c 2002 300 0
c 2003 200 0
c 2004 150 0

 

[Claus Busch]

Hi again,

Am Wed, 29 Jan 2014 10:13:06 -0800 (PST) schrieb (e-mail address removed):

Tx but with the workbook provided I have the same results if I change the starting date of the product b from 200 to 1999, see below

sorry but I didn't know that you have different minimums for the
categories.
In D2 try:
=IF(B2=MIN(IF($A$2:$A$100=A2,$B$2:$B$100)),SUMIF($A$2:$A$100,A2,$C$2:$C$100),0)
ans enter the array formula with CTRL+Shift+Enter


Regards
Claus B.

 

[misterdtc]

Dear Claus,

thank you for the array formula, I had not thought about that, works like a charm.

Thank you so much