MAX figure within a date range as a function of today()'s date

Column B = All dates for this year.
Column H = production figures entered daily.

My goal is to come up with a cell that calculates the max daily production
figure for the month by referencing today's date to find which month to
consider.

I am using a sumif formula to calculate the month's cumulative production
figure but haven't figured out how to find the highest daily figure of the
month.

=SUMIF(B3:B262,TEXT(TODAY(),"mmm"),H3:H262) ...

Basically in need a MAX version of the above formula.

Thanks!

Try:

=MAX((MONTH(B1:B1000)=MONTH(TODAY()))*(H1:H1000))

This ia an array formula so enter it with Ctrl + Shift + Enter not just
enter.

--
HTH

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk


"irvine79" <[email protected]> wrote in message
news:[email protected]...
> Column B = All dates for this year.
> Column H = production figures entered daily.
>
> My goal is to come up with a cell that calculates the max daily production
> figure for the month by referencing today's date to find which month to
> consider.
>
> I am using a sumif formula to calculate the month's cumulative production
> figure but haven't figured out how to find the highest daily figure of the
> month.
>
> =SUMIF(B3:B262,TEXT(TODAY(),"mmm"),H3:H262) ...
>
> Basically in need a MAX version of the above formula.
>
> Thanks!

Thanks, worked perfect! One more question, what does Ctrl + Shift + Enter
accomplish?

thanks

"Sandy Mann" wrote:

> Try:
>
> =MAX((MONTH(B1:B1000)=MONTH(TODAY()))*(H1:H1000))
>
> This ia an array formula so enter it with Ctrl + Shift + Enter not just
> enter.
>
> --
> HTH
>
> Sandy
> In Perth, the ancient capital of Scotland
>
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "irvine79" <[email protected]> wrote in message
> news:[email protected]...
> > Column B = All dates for this year.
> > Column H = production figures entered daily.
> >
> > My goal is to come up with a cell that calculates the max daily production
> > figure for the month by referencing today's date to find which month to
> > consider.
> >
> > I am using a sumif formula to calculate the month's cumulative production
> > figure but haven't figured out how to find the highest daily figure of the
> > month.
> >
> > =SUMIF(B3:B262,TEXT(TODAY(),"mmm"),H3:H262) ...
> >
> > Basically in need a MAX version of the above formula.
> >
> > Thanks!
>
>
>

"irvine79" <[email protected]> wrote in message
news:[email protected]...
> Thanks, worked perfect! One more question, what does Ctrl + Shift + Enter
> accomplish?
>

It makes Excel work on each elemant of the first array with the first
element of the second array. ie:

=SUM((A1:A3)*(B1:B3))

work out as

=SUM(A1*B1,A2*B2,A3*B3)

--
HTH

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk


"irvine79" <[email protected]> wrote in message
news:[email protected]...
> Thanks, worked perfect! One more question, what does Ctrl + Shift + Enter
> accomplish?
>
> thanks
>
> "Sandy Mann" wrote:
>
>> Try:
>>
>> =MAX((MONTH(B1:B1000)=MONTH(TODAY()))*(H1:H1000))
>>
>> This ia an array formula so enter it with Ctrl + Shift + Enter not just
>> enter.
>>
>> --
>> HTH
>>
>> Sandy
>> In Perth, the ancient capital of Scotland
>>
>> [email protected]
>> [email protected] with @tiscali.co.uk
>>
>>
>> "irvine79" <[email protected]> wrote in message
>> news:[email protected]...
>> > Column B = All dates for this year.
>> > Column H = production figures entered daily.
>> >
>> > My goal is to come up with a cell that calculates the max daily
>> > production
>> > figure for the month by referencing today's date to find which month to
>> > consider.
>> >
>> > I am using a sumif formula to calculate the month's cumulative
>> > production
>> > figure but haven't figured out how to find the highest daily figure of
>> > the
>> > month.
>> >
>> > =SUMIF(B3:B262,TEXT(TODAY(),"mmm"),H3:H262) ...
>> >
>> > Basically in need a MAX version of the above formula.
>> >
>> > Thanks!
>>
>>
>>