maxif
looking for a formula similar to 'sumif', however i need a 'maxif' that
compares a range and if true then selects the largest date in another range. I'm using excel 2003. For example (see below) =maxif(a1:a4=280, b1:b4). I'm looking to get 280 & 01-20-05 as the answer. I'm using maxif as if it is real...but it isn't. help! thanks A B 1 001 01-01-05 2 280 12-31-04 3 500 11-01-04 4 280 01-20-05 |
One way
=MAX((A1:A4=280)*(B1:B4)) entered with ctrl + shift & enter Regards, Peo Sjoblom "Gregg" wrote: looking for a formula similar to 'sumif', however i need a 'maxif' that compares a range and if true then selects the largest date in another range. I'm using excel 2003. For example (see below) =maxif(a1:a4=280, b1:b4). I'm looking to get 280 & 01-20-05 as the answer. I'm using maxif as if it is real...but it isn't. help! thanks A B 1 001 01-01-05 2 280 12-31-04 3 500 11-01-04 4 280 01-20-05 |
Is this non-array "easier" on XL Peo?
=SUMPRODUCT(MAX((A2:A6=280)*(B2:B6))) -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit! ------------------------------------------------------------------- "Peo Sjoblom" wrote in message ... One way =MAX((A1:A4=280)*(B1:B4)) entered with ctrl + shift & enter Regards, Peo Sjoblom "Gregg" wrote: looking for a formula similar to 'sumif', however i need a 'maxif' that compares a range and if true then selects the largest date in another range. I'm using excel 2003. For example (see below) =maxif(a1:a4=280, b1:b4). I'm looking to get 280 & 01-20-05 as the answer. I'm using maxif as if it is real...but it isn't. help! thanks A B 1 001 01-01-05 2 280 12-31-04 3 500 11-01-04 4 280 01-20-05 |
It's not easier on my typing fingers that's for sure, even w/o the ctrl
shift enter <bg Peo "RagDyer" wrote: Is this non-array "easier" on XL Peo? =SUMPRODUCT(MAX((A2:A6=280)*(B2:B6))) -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit! ------------------------------------------------------------------- "Peo Sjoblom" wrote in message ... One way =MAX((A1:A4=280)*(B1:B4)) entered with ctrl + shift & enter Regards, Peo Sjoblom "Gregg" wrote: looking for a formula similar to 'sumif', however i need a 'maxif' that compares a range and if true then selects the largest date in another range. I'm using excel 2003. For example (see below) =maxif(a1:a4=280, b1:b4). I'm looking to get 280 & 01-20-05 as the answer. I'm using maxif as if it is real...but it isn't. help! thanks A B 1 001 01-01-05 2 280 12-31-04 3 500 11-01-04 4 280 01-20-05 |
Ha Ha <vbg
BUT ... was an honest question. Do you know if the array SumProduct is less taxing on XL then a formula entered with CSE? -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit! ------------------------------------------------------------------- "Peo Sjoblom" wrote in message ... It's not easier on my typing fingers that's for sure, even w/o the ctrl shift enter <bg Peo "RagDyer" wrote: Is this non-array "easier" on XL Peo? =SUMPRODUCT(MAX((A2:A6=280)*(B2:B6))) -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit! ------------------------------------------------------------------- "Peo Sjoblom" wrote in message ... One way =MAX((A1:A4=280)*(B1:B4)) entered with ctrl + shift & enter Regards, Peo Sjoblom "Gregg" wrote: looking for a formula similar to 'sumif', however i need a 'maxif' that compares a range and if true then selects the largest date in another range. I'm using excel 2003. For example (see below) =maxif(a1:a4=280, b1:b4). I'm looking to get 280 & 01-20-05 as the answer. I'm using maxif as if it is real...but it isn't. help! thanks A B 1 001 01-01-05 2 280 12-31-04 3 500 11-01-04 4 280 01-20-05 |
All times are GMT +1. The time now is 01:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com