MaxIFs help

I have several SumIFS commands that work great... However for some columns I need to get the Maximum value for a specific range...

for example on my attached works sheet in cell D18 on my Calcs tab I have the following (=Calcs!D18)

I have

=SUMIFS('RA Data'!D:D,'RA Data'!$B:$B,">=1/8/2016 12:00:00 AM",'RA Data'!$B:$B, "<=1/8/2016 11:00:59 PM")

It works greats - it give the total of the D column on my RA Data Tab when the date range (B column) is between a date range.

What I need is to find the max or highest value of the D column when it's between the same range...

I have tried =Max(IF and SumProduct - but so far no luck... Help

I guess you're editing every sumifs formula manually

In C18 and filled down,

=SUMIFS('RA Data'!C:C,'RA Data'!$B:$B,">="&B18,'RA Data'!$B:$B, "<"&B18+1)

You could apply the same method to the other columns.

I'll also point out that this method is less prone to failure if you send the data to other users, bearing in mind that I'm using UK regional settings, with dates formatted as d/m/y, your method returns incorrect results when I open the sheet, this happens because the dates in your sumifs criteria are text strings, not real (numeric) dates.

For your MAX formula, try something on the lines of

=MAX(IF(INT('RA Data'!$B$2:$B$2521)=B18,'RA Data'!$C$2:$C$2521))

Which needs to be confirmed as an array formula by pressing Shift Ctrl and Enter, not just Enter.

Note that I have used a limited number of rows, not entire columns. Using entire columns would result in slow calculation and possible errors. If you need to allow for expansion of the data range then the use of dynamic ranges would be advisable.

on the first part ie
=SUMIFS('RA Data'!C:C,'RA Data'!$B:$B,">="&B18,'RA Data'!$B:$B, "<"&B18+1)
that works great... didn't know I could
Thanks
on the second part
right now the
=MAX(IF(INT('RA Data'!$B$2:$B$2521)=B18,'RA Data'!$D$2:$D$2521))
gives me the max on everything in the D column which is nice - but I need to work it to the a single day range ie the value in B18 - ie the max for B18...
However, this gives me something to start with...

issue with the array.. yep.. oops

Thanks and the pivot table is awesome.. thanks

Originally Posted by Hondahawkrider

However I had one quick question -

when you provided this

=MAX(IF(INT('RA Data'!$B$2:$B$2521)=B18,'RA Data'!$D$2:$D$2521))

is there anyway to set a range ... say I wanted the max value on a range of dates - that are in b18 thru b22 or something.. I tried adding a range but it didn't work out ..

Anyway thanks again

If the range of dates is consecutive, you could use

=MAX(IF(INT('RA Data'!$B$2:$B$2521)>=B18,IF(INT('RA Data'!$B$2:$B$2521)<(B22+1),'RA Data'!$C$2:$C$2521)))

Or if the dates are not consecutive, try

=MAX(IF(ISNUMBER(MATCH(INT('RA Data'!$B$2:$B$2521),B18:B22,0)),'RA Data'!$C$2:$C$2521))

Note that both of these formulas need to be array confirmed with Shift Ctrl Enter.

Another option would be to filter the pivottable.