ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   maxif (https://www.excelbanter.com/excel-worksheet-functions/8368-maxif.html)

Gregg

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

Peo Sjoblom

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


RagDyer

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



Peo Sjoblom

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




RagDyer

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