MAXIF?

[Carrie_Loos via OfficeKB.com]

I can't figure out this array formula - can anyone assist?

Assume it is column A, B, C and row 2 starts the info I am reviewing. I want
to find the maximum date for any value in column b that has a value greater
than zero. The issue I am having is there are multiple months and multiple
people so a "SUMIF" type of formula would work but I can't figure out the
formula to do a "MAXIF" type of function. Any help would be greatly
appreciated. Thx Carrie

Example data:

Sum per line unique Date
AnthonyJan-08 0.0 01/01/08
AnthonyJan-08 0.0 01/02/08
AnthonyJan-08 0.0 01/03/08
AnthonyJan-08 0.0 01/04/08
AnthonyJan-08 0.0 01/05/08
AnthonyJan-08 0.0 01/06/08
AnthonyJan-08 0.0 01/07/08
AnthonyJan-08 0.0 01/08/08
AnthonyJan-08 0.0 01/09/08
AnthonyJan-08 0.0 01/10/08
AnthonyJan-08 0.0 01/11/08
AnthonyJan-08 0.0 01/12/08
AnthonyJan-08 0.0 01/13/08
AnthonyJan-08 0.0 01/14/08
AnthonyJan-08 0.0 01/15/08
AnthonyJan-08 0.0 01/16/08
AnthonyJan-08 2.0 01/17/08
AnthonyJan-08 2.0 01/18/08
AnthonyJan-08 2.0 01/19/08
AnthonyJan-08 2.0 01/20/08
AnthonyJan-08 2.0 01/21/08
AnthonyJan-08 2.0 01/22/08
AnthonyJan-08 0.0 01/23/08
AnthonyJan-08 0.0 01/24/08
AnthonyJan-08 2.0 01/25/08
AnthonyJan-08 2.0 01/26/08
AnthonyJan-08 2.0 01/27/08
AnthonyJan-08 2.0 01/28/08
AnthonyJan-08 0.0 01/29/08
AnthonyJan-08 0.0 01/30/08
AnthonyJan-08 0.0 01/31/08
AnthonyFeb-08 2.0 02/01/08
AnthonyFeb-08 0.0 02/02/08
AnthonyFeb-08 0.0 02/03/08
AnthonyFeb-08 2.0 02/04/08
AnthonyFeb-08 2.0 02/05/08
AnthonyFeb-08 0.0 02/06/08
AnthonyFeb-08 2.0 02/07/08
AnthonyFeb-08 2.0 02/08/08

 

[Rick Rothstein]

Try this array-entered** formula...

=MAX(IF(B2:B100<>0,C2:C100,""))

**Commit the formula with Ctrl+Shift+Enter, not just Enter by itself.

Change the upper end of the ranges from 100 to the maximum row number that
will ever contain data.

 

[Don Guillett]

Basic idea. Modify to suit and be sure to enter using ctrl+shift+enter
=MAX(IF(D121="a",E1:E21))

 

[Carrie_Loos via OfficeKB.com]

Thanks but my issue is to try and get the max date in the range only for
those unique instances. Example: any that are labeled "AnthonyJan-08" then
another max for any that are labeled "AnthonyFeb-08". With a SUMIF I can
point to a cell and define the range to look in by that cell value. Secondly,
since my range cycles through different people and all the calendar months I
was hoping not to have to rewrite the formula over and over again like a
SUMIF. Is it possible?

Try this array-entered** formula...

=MAX(IF(B2:B100<>0,C2:C100,""))

**Commit the formula with Ctrl+Shift+Enter, not just Enter by itself.

Change the upper end of the ranges from 100 to the maximum row number that
will ever contain data.

I can't figure out this array formula - can anyone assist?

[quoted text clipped - 49 lines]

AnthonyFeb-08 2.0 02/07/08
AnthonyFeb-08 2.0 02/08/08

 

[Rick Rothstein]

It is hard to know from your postings if you know in advance all the
possible entry values in Column A or not. Assuming you don't, will this be
acceptable? Place this formula...

=IF(A2="","",IF(COUNTIF(A$2:A2,A2)>1,"",MAX(IF((B$2:B$100<>0)*(A$2:A$100=A2),C$2:C$100,""))))

In Row 2 of some unused column and copy it down... the first occurrence of a
value in Column A will show the maximum date you are looking for.

--
Rick (MVP - Excel)

Thanks but my issue is to try and get the max date in the range only for
those unique instances. Example: any that are labeled "AnthonyJan-08" then
another max for any that are labeled "AnthonyFeb-08". With a SUMIF I can
point to a cell and define the range to look in by that cell value.
Secondly,
since my range cycles through different people and all the calendar months
I
was hoping not to have to rewrite the formula over and over again like a
SUMIF. Is it possible?

Try this array-entered** formula...

=MAX(IF(B2:B100<>0,C2:C100,""))

**Commit the formula with Ctrl+Shift+Enter, not just Enter by itself.

Change the upper end of the ranges from 100 to the maximum row number that
will ever contain data.

I can't figure out this array formula - can anyone assist?

[quoted text clipped - 49 lines]

AnthonyFeb-08 2.0 02/07/08
AnthonyFeb-08 2.0 02/08/08

 

[Tom Hutchins]

Try this (assumes your data is sorted by column A):

In D2 enter
=IF(A2=A1,D1,D1+1)
and copy down.

In E2 enter
=MAX(IF(($B$2:$B$40>0)*($D$2:$D$40=D2),$C$2:$C$40,0))
and copy down. THis is an array formula, so instead os Enter you need to
pres Ctrl+Shift+Enter to commit the formula.

In F2 enter
=IF(D2<>D1,E2,"")
and copy down.

Hope this helps,

Hutch

Thanks but my issue is to try and get the max date in the range only for
those unique instances. Example: any that are labeled "AnthonyJan-08" then
another max for any that are labeled "AnthonyFeb-08". With a SUMIF I can
point to a cell and define the range to look in by that cell value. Secondly,
since my range cycles through different people and all the calendar months I
was hoping not to have to rewrite the formula over and over again like a
SUMIF. Is it possible?

Try this array-entered** formula...

=MAX(IF(B2:B100<>0,C2:C100,""))

**Commit the formula with Ctrl+Shift+Enter, not just Enter by itself.

Change the upper end of the ranges from 100 to the maximum row number that
will ever contain data.

I can't figure out this array formula - can anyone assist?

[quoted text clipped - 49 lines]

AnthonyFeb-08 2.0 02/07/08
AnthonyFeb-08 2.0 02/08/08

 

[Carrie_Loos via OfficeKB.com]

Thank you - Both ideas in the post worked well and gave me some terrific
ideas for other data I need to pull in this excercise. I love to learn new
things!

It is hard to know from your postings if you know in advance all the
possible entry values in Column A or not. Assuming you don't, will this be
acceptable? Place this formula...

=IF(A2="","",IF(COUNTIF(A$2:A2,A2)>1,"",MAX(IF((B$2:B$100<>0)*(A$2:A$100=A2),C$2:C$100,""))))

In Row 2 of some unused column and copy it down... the first occurrence of a
value in Column A will show the maximum date you are looking for.

Thanks but my issue is to try and get the max date in the range only for
those unique instances. Example: any that are labeled "AnthonyJan-08" then

[quoted text clipped - 20 lines]