I have previously used the "choose" function for sum functions that would sum
a data range for a month-to-date display. Recently we have found that one of
the updates we have ran has limited the number of agruments that the choose
function supports has been limited to 28 arguments. The problem I face is
29, 30, and 31 day months become a problem. We have not been able to
uninstall the update (assuming that this change was made with a Service Pack
update). Does anybody have suggestions on how to accomplish the same result
with a different formula?

A couple of items to help try and explain.

On the 14th day I would like to see a month to date total from the 1st to
the 14th.
On the 30th day I would like to see a month to date total from the 1st to
the 30th.

These would be simple to fix with a sum function, however, in the same sheet
I also preload daily data from the prior year, so the sum function would
always show the total from the 1st to the end of the month.

I also have a date key that we use to drive which day's data is shown so
that it is possible for me to quickly change the data that is displayed to
any day I select. this made it possible for me to see data through the 14th
of the month, even though data had been entered say to the 21st or beyond.

If it is any help, here is an example of how the formula was written prior
to the limitation of the number of arguments in the choose function (forgive
the length):

=CHOOSE(($D$3)+1,0,SUM(U7:U7),SUM(U7:V7),SUM(U7:W7),SUM(U7:X7),SUM(U7:Y7),SUM(U7:Z7),SUM(U7:AA7),SUM(U7:AB7),SUM(U7:AC7),SUM(U7:AD7),SUM(U7:AE7),SUM(U7:AF7),SUM(U7:AG7),SUM(U7:AH7),SUM(U7:AI7),SUM(U7:AJ7),SUM(U7:AK7),SUM(U7:AL7),SUM(U7:AM7),SUM(U7:AN7),SUM(U7:AO7),SUM(U7:AP7),SUM(U7:AQ7),SUM(U7:AR7),SUM(U7:AS7),SUM(U7:AT7),SUM(U7:AU7),SUM(U7:AV7),SUM(U7:AW7),SUM(U7:AX7),SUM(U7:AY7))

The reference to $D$3 is to the date key that displays which number of day
we are in for the month (or which day I would like to see totals through).

Thanks

try this idea instead
=SUM(U7:OFFSET(U7,0,d3-1))



--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"dkcpa" <(E-Mail Removed)> wrote in message
news:1B4B4649-6219-428F-9FFD-(E-Mail Removed)...
>I have previously used the "choose" function for sum functions that would
>sum
> a data range for a month-to-date display. Recently we have found that one
> of
> the updates we have ran has limited the number of agruments that the
> choose
> function supports has been limited to 28 arguments. The problem I face is
> 29, 30, and 31 day months become a problem. We have not been able to
> uninstall the update (assuming that this change was made with a Service
> Pack
> update). Does anybody have suggestions on how to accomplish the same
> result
> with a different formula?
>
> A couple of items to help try and explain.
>
> On the 14th day I would like to see a month to date total from the 1st to
> the 14th.
> On the 30th day I would like to see a month to date total from the 1st to
> the 30th.
>
> These would be simple to fix with a sum function, however, in the same
> sheet
> I also preload daily data from the prior year, so the sum function would
> always show the total from the 1st to the end of the month.
>
> I also have a date key that we use to drive which day's data is shown so
> that it is possible for me to quickly change the data that is displayed to
> any day I select. this made it possible for me to see data through the
> 14th
> of the month, even though data had been entered say to the 21st or beyond.
>
> If it is any help, here is an example of how the formula was written prior
> to the limitation of the number of arguments in the choose function
> (forgive
> the length):
>
> =CHOOSE(($D$3)+1,0,SUM(U7:U7),SUM(U7:V7),SUM(U7:W7),SUM(U7:X7),SUM(U7:Y7),SUM(U7:Z7),SUM(U7:AA7),SUM(U7:AB7),SUM(U7:AC7),SUM(U7:AD7),SUM(U7:AE7),SUM(U7:AF7),SUM(U7:AG7),SUM(U7:AH7),SUM(U7:AI7),SUM(U7:AJ7),SUM(U7:AK7),SUM(U7:AL7),SUM(U7:AM7),SUM(U7:AN7),SUM(U7:AO7),SUM(U7:AP7),SUM(U7:AQ7),SUM(U7:AR7),SUM(U7:AS7),SUM(U7:AT7),SUM(U7:AU7),SUM(U7:AV7),SUM(U7:AW7),SUM(U7:AX7),SUM(U7:AY7))
>
> The reference to $D$3 is to the date key that displays which number of day
> we are in for the month (or which day I would like to see totals through).
>
> Thanks

Also, pls post in ONE group only!!!

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Don Guillett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> try this idea instead
> =SUM(U7:OFFSET(U7,0,d3-1))
>
>
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "dkcpa" <(E-Mail Removed)> wrote in message
> news:1B4B4649-6219-428F-9FFD-(E-Mail Removed)...
>>I have previously used the "choose" function for sum functions that would
>>sum
>> a data range for a month-to-date display. Recently we have found that
>> one of
>> the updates we have ran has limited the number of agruments that the
>> choose
>> function supports has been limited to 28 arguments. The problem I face
>> is
>> 29, 30, and 31 day months become a problem. We have not been able to
>> uninstall the update (assuming that this change was made with a Service
>> Pack
>> update). Does anybody have suggestions on how to accomplish the same
>> result
>> with a different formula?
>>
>> A couple of items to help try and explain.
>>
>> On the 14th day I would like to see a month to date total from the 1st to
>> the 14th.
>> On the 30th day I would like to see a month to date total from the 1st to
>> the 30th.
>>
>> These would be simple to fix with a sum function, however, in the same
>> sheet
>> I also preload daily data from the prior year, so the sum function would
>> always show the total from the 1st to the end of the month.
>>
>> I also have a date key that we use to drive which day's data is shown so
>> that it is possible for me to quickly change the data that is displayed
>> to
>> any day I select. this made it possible for me to see data through the
>> 14th
>> of the month, even though data had been entered say to the 21st or
>> beyond.
>>
>> If it is any help, here is an example of how the formula was written
>> prior
>> to the limitation of the number of arguments in the choose function
>> (forgive
>> the length):
>>
>> =CHOOSE(($D$3)+1,0,SUM(U7:U7),SUM(U7:V7),SUM(U7:W7),SUM(U7:X7),SUM(U7:Y7),SUM(U7:Z7),SUM(U7:AA7),SUM(U7:AB7),SUM(U7:AC7),SUM(U7:AD7),SUM(U7:AE7),SUM(U7:AF7),SUM(U7:AG7),SUM(U7:AH7),SUM(U7:AI7),SUM(U7:AJ7),SUM(U7:AK7),SUM(U7:AL7),SUM(U7:AM7),SUM(U7:AN7),SUM(U7:AO7),SUM(U7:AP7),SUM(U7:AQ7),SUM(U7:AR7),SUM(U7:AS7),SUM(U7:AT7),SUM(U7:AU7),SUM(U7:AV7),SUM(U7:AW7),SUM(U7:AX7),SUM(U7:AY7))
>>
>> The reference to $D$3 is to the date key that displays which number of
>> day
>> we are in for the month (or which day I would like to see totals
>> through).
>>
>> Thanks
>

First time I have posted anything and realized too late this was probably the
wrong group. would have deleted it if i knew how.

thanks for the help.

"Don Guillett" wrote:

> Also, pls post in ONE group only!!!
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Don Guillett" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > try this idea instead
> > =SUM(U7:OFFSET(U7,0,d3-1))
> >
> >
> >
> > --
> > Don Guillett
> > Microsoft MVP Excel
> > SalesAid Software
> > (E-Mail Removed)
> > "dkcpa" <(E-Mail Removed)> wrote in message
> > news:1B4B4649-6219-428F-9FFD-(E-Mail Removed)...
> >>I have previously used the "choose" function for sum functions that would
> >>sum
> >> a data range for a month-to-date display. Recently we have found that
> >> one of
> >> the updates we have ran has limited the number of agruments that the
> >> choose
> >> function supports has been limited to 28 arguments. The problem I face
> >> is
> >> 29, 30, and 31 day months become a problem. We have not been able to
> >> uninstall the update (assuming that this change was made with a Service
> >> Pack
> >> update). Does anybody have suggestions on how to accomplish the same
> >> result
> >> with a different formula?
> >>
> >> A couple of items to help try and explain.
> >>
> >> On the 14th day I would like to see a month to date total from the 1st to
> >> the 14th.
> >> On the 30th day I would like to see a month to date total from the 1st to
> >> the 30th.
> >>
> >> These would be simple to fix with a sum function, however, in the same
> >> sheet
> >> I also preload daily data from the prior year, so the sum function would
> >> always show the total from the 1st to the end of the month.
> >>
> >> I also have a date key that we use to drive which day's data is shown so
> >> that it is possible for me to quickly change the data that is displayed
> >> to
> >> any day I select. this made it possible for me to see data through the
> >> 14th
> >> of the month, even though data had been entered say to the 21st or
> >> beyond.
> >>
> >> If it is any help, here is an example of how the formula was written
> >> prior
> >> to the limitation of the number of arguments in the choose function
> >> (forgive
> >> the length):
> >>
> >> =CHOOSE(($D$3)+1,0,SUM(U7:U7),SUM(U7:V7),SUM(U7:W7),SUM(U7:X7),SUM(U7:Y7),SUM(U7:Z7),SUM(U7:AA7),SUM(U7:AB7),SUM(U7:AC7),SUM(U7:AD7),SUM(U7:AE7),SUM(U7:AF7),SUM(U7:AG7),SUM(U7:AH7),SUM(U7:AI7),SUM(U7:AJ7),SUM(U7:AK7),SUM(U7:AL7),SUM(U7:AM7),SUM(U7:AN7),SUM(U7:AO7),SUM(U7:AP7),SUM(U7:AQ7),SUM(U7:AR7),SUM(U7:AS7),SUM(U7:AT7),SUM(U7:AU7),SUM(U7:AV7),SUM(U7:AW7),SUM(U7:AX7),SUM(U7:AY7))
> >>
> >> The reference to $D$3 is to the date key that displays which number of
> >> day
> >> we are in for the month (or which day I would like to see totals
> >> through).
> >>
> >> Thanks
> >
>
>