Date Format Inside of a SUMIF Statement

Greetings,

I am trying to get the sum for each month in a column which has thee
entire year.

So far I have:

SUMIF($B:$B,{need month and year here},$DT:$DT)

B is formatted as mm/dd/yy I need mm/yy in the formula. I can't
remember how to do this! Anyone have any ideas?

Any help would be appreciated.

TIA

-Minitman

Minitman wrote:
> Greetings,
>
> I am trying to get the sum for each month in a column which has thee
> entire year.
>
> So far I have:
>
> SUMIF($B:$B,{need month and year here},$DT:$DT)
>
> B is formatted as mm/dd/yy I need mm/yy in the formula. I can't
> remember how to do this! Anyone have any ideas?
>
> Any help would be appreciated.
>
> TIA
>
> -Minitman

=SUMIF(B:B,">="&A2,DT:DT)-SUMIF(B:B,">"&A3,DT:DT)

where A2 is a first day date of the mont/year of interest like 1-Mar-04
and A3 houses: =EOMONTH(A2,0).

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Minitman wrote:
> Greetings,
>
> I am trying to get the sum for each month in a column which has thee
> entire year.
>
> So far I have:
>
> SUMIF($B:$B,{need month and year here},$DT:$DT)
>
> B is formatted as mm/dd/yy I need mm/yy in the formula. I can't
> remember how to do this! Anyone have any ideas?
>
> Any help would be appreciated.
>
> TIA
>
> -Minitman

=SUMIF(B:B,">="&A2,DT:DT)-SUMIF(B:B,">"&A3,DT:DT)

where A2 is a first day date of the mont/year of interest like 1-Mar-04
and A3 houses: =EOMONTH(A2,0).

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Give example of your data and expected answer.

Mangesh


"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
news:4q3qa19nau65sh6e7j57ct6jgltmju59m7@4ax.com...
> Greetings,
>
> I am trying to get the sum for each month in a column which has thee
> entire year.
>
> So far I have:
>
> SUMIF($B:$B,{need month and year here},$DT:$DT)
>
> B is formatted as mm/dd/yy I need mm/yy in the formula. I can't
> remember how to do this! Anyone have any ideas?
>
> Any help would be appreciated.
>
> TIA
>
> -Minitman

Give example of your data and expected answer.

Mangesh


"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
news:4q3qa19nau65sh6e7j57ct6jgltmju59m7@4ax.com...
> Greetings,
>
> I am trying to get the sum for each month in a column which has thee
> entire year.
>
> So far I have:
>
> SUMIF($B:$B,{need month and year here},$DT:$DT)
>
> B is formatted as mm/dd/yy I need mm/yy in the formula. I can't
> remember how to do this! Anyone have any ideas?
>
> Any help would be appreciated.
>
> TIA
>
> -Minitman

Hey Mangesh,

B DT
3 1/20/04 $4.00
4 1/21/04 $3.00
5 1/29/04 $2.00
6 2/14/04 $1.00
7 3/12/04 $11.00
8 3/16/04 $6.00
9 2/22/04 $20.00


On a different sheet:

Month Amount
Jan (Formula goes here, should return $9.00)
Feb (Formula goes here, should return $21.00)
Mar (Formula goes here, should return $17.00)

That is what I am trying to do

Any suggestions?

-Minitman
On Mon, 13 Jun 2005 10:33:55 +0530, "Mangesh Yadav"
<m6angesh.NOSPAMyadav@gmail.com> wrote:

>Give example of your data and expected answer.
>
>Mangesh
>
>
>"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
>news:4q3qa19nau65sh6e7j57ct6jgltmju59m7@4ax.com...
>> Greetings,
>>
>> I am trying to get the sum for each month in a column which has thee
>> entire year.
>>
>> So far I have:
>>
>> SUMIF($B:$B,{need month and year here},$DT:$DT)
>>
>> B is formatted as mm/dd/yy I need mm/yy in the formula. I can't
>> remember how to do this! Anyone have any ideas?
>>
>> Any help would be appreciated.
>>
>> TIA
>>
>> -Minitman
>

Hey Mangesh,

B DT
3 1/20/04 $4.00
4 1/21/04 $3.00
5 1/29/04 $2.00
6 2/14/04 $1.00
7 3/12/04 $11.00
8 3/16/04 $6.00
9 2/22/04 $20.00


On a different sheet:

Month Amount
Jan (Formula goes here, should return $9.00)
Feb (Formula goes here, should return $21.00)
Mar (Formula goes here, should return $17.00)

That is what I am trying to do

Any suggestions?

-Minitman
On Mon, 13 Jun 2005 10:33:55 +0530, "Mangesh Yadav"
<m6angesh.NOSPAMyadav@gmail.com> wrote:

>Give example of your data and expected answer.
>
>Mangesh
>
>
>"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
>news:4q3qa19nau65sh6e7j57ct6jgltmju59m7@4ax.com...
>> Greetings,
>>
>> I am trying to get the sum for each month in a column which has thee
>> entire year.
>>
>> So far I have:
>>
>> SUMIF($B:$B,{need month and year here},$DT:$DT)
>>
>> B is formatted as mm/dd/yy I need mm/yy in the formula. I can't
>> remember how to do this! Anyone have any ideas?
>>
>> Any help would be appreciated.
>>
>> TIA
>>
>> -Minitman
>

=SUMPRODUCT(--(CHOOSE(MONTH($B$1:$B$7),"Jan","Feb","Mar")=E1),$DT$1:$DT$7)

confirm with control shift enter

Mangesh





"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
news:fg5qa11sevd033si63f3q4uiho3cj71aoi@4ax.com...
> Hey Mangesh,
>
> B DT
> 3 1/20/04 $4.00
> 4 1/21/04 $3.00
> 5 1/29/04 $2.00
> 6 2/14/04 $1.00
> 7 3/12/04 $11.00
> 8 3/16/04 $6.00
> 9 2/22/04 $20.00
>
>
> On a different sheet:
>
> Month Amount
> Jan (Formula goes here, should return $9.00)
> Feb (Formula goes here, should return $21.00)
> Mar (Formula goes here, should return $17.00)
>
> That is what I am trying to do
>
> Any suggestions?
>
> -Minitman
> On Mon, 13 Jun 2005 10:33:55 +0530, "Mangesh Yadav"
> <m6angesh.NOSPAMyadav@gmail.com> wrote:
>
> >Give example of your data and expected answer.
> >
> >Mangesh
> >
> >
> >"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
> >news:4q3qa19nau65sh6e7j57ct6jgltmju59m7@4ax.com...
> >> Greetings,
> >>
> >> I am trying to get the sum for each month in a column which has thee
> >> entire year.
> >>
> >> So far I have:
> >>
> >> SUMIF($B:$B,{need month and year here},$DT:$DT)
> >>
> >> B is formatted as mm/dd/yy I need mm/yy in the formula. I can't
> >> remember how to do this! Anyone have any ideas?
> >>
> >> Any help would be appreciated.
> >>
> >> TIA
> >>
> >> -Minitman
> >
>

=SUMPRODUCT(--(CHOOSE(MONTH($B$1:$B$7),"Jan","Feb","Mar")=E1),$DT$1:$DT$7)

confirm with control shift enter

Mangesh





"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
news:fg5qa11sevd033si63f3q4uiho3cj71aoi@4ax.com...
> Hey Mangesh,
>
> B DT
> 3 1/20/04 $4.00
> 4 1/21/04 $3.00
> 5 1/29/04 $2.00
> 6 2/14/04 $1.00
> 7 3/12/04 $11.00
> 8 3/16/04 $6.00
> 9 2/22/04 $20.00
>
>
> On a different sheet:
>
> Month Amount
> Jan (Formula goes here, should return $9.00)
> Feb (Formula goes here, should return $21.00)
> Mar (Formula goes here, should return $17.00)
>
> That is what I am trying to do
>
> Any suggestions?
>
> -Minitman
> On Mon, 13 Jun 2005 10:33:55 +0530, "Mangesh Yadav"
> <m6angesh.NOSPAMyadav@gmail.com> wrote:
>
> >Give example of your data and expected answer.
> >
> >Mangesh
> >
> >
> >"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
> >news:4q3qa19nau65sh6e7j57ct6jgltmju59m7@4ax.com...
> >> Greetings,
> >>
> >> I am trying to get the sum for each month in a column which has thee
> >> entire year.
> >>
> >> So far I have:
> >>
> >> SUMIF($B:$B,{need month and year here},$DT:$DT)
> >>
> >> B is formatted as mm/dd/yy I need mm/yy in the formula. I can't
> >> remember how to do this! Anyone have any ideas?
> >>
> >> Any help would be appreciated.
> >>
> >> TIA
> >>
> >> -Minitman
> >
>

Another thing, in the formula, enter apr, may, ... and so on till dec.
Also the cell E1 in my formula conatins the text "Jan" and not a date
formatted as MMM.

If it has a date formatted as MMM, then you need to replace the
CHOOSE(MONTH($B$1:$B$7),"Jan","Feb","Mar")=E1
with
MONTH($B$1:$B$7)=E1


Mangesh



"Mangesh Yadav" <mangesh.NOSPAMyadav@gmail.com> wrote in message
news:OJsTbo9bFHA.3120@TK2MSFTNGP12.phx.gbl...
> =SUMPRODUCT(--(CHOOSE(MONTH($B$1:$B$7),"Jan","Feb","Mar")=E1),$DT$1:$DT$7)
>
> confirm with control shift enter
>
> Mangesh
>
>
>
>
>
> "Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
> news:fg5qa11sevd033si63f3q4uiho3cj71aoi@4ax.com...
> > Hey Mangesh,
> >
> > B DT
> > 3 1/20/04 $4.00
> > 4 1/21/04 $3.00
> > 5 1/29/04 $2.00
> > 6 2/14/04 $1.00
> > 7 3/12/04 $11.00
> > 8 3/16/04 $6.00
> > 9 2/22/04 $20.00
> >
> >
> > On a different sheet:
> >
> > Month Amount
> > Jan (Formula goes here, should return $9.00)
> > Feb (Formula goes here, should return $21.00)
> > Mar (Formula goes here, should return $17.00)
> >
> > That is what I am trying to do
> >
> > Any suggestions?
> >
> > -Minitman
> > On Mon, 13 Jun 2005 10:33:55 +0530, "Mangesh Yadav"
> > <m6angesh.NOSPAMyadav@gmail.com> wrote:
> >
> > >Give example of your data and expected answer.
> > >
> > >Mangesh
> > >
> > >
> > >"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
> > >news:4q3qa19nau65sh6e7j57ct6jgltmju59m7@4ax.com...
> > >> Greetings,
> > >>
> > >> I am trying to get the sum for each month in a column which has thee
> > >> entire year.
> > >>
> > >> So far I have:
> > >>
> > >> SUMIF($B:$B,{need month and year here},$DT:$DT)
> > >>
> > >> B is formatted as mm/dd/yy I need mm/yy in the formula. I can't
> > >> remember how to do this! Anyone have any ideas?
> > >>
> > >> Any help would be appreciated.
> > >>
> > >> TIA
> > >>
> > >> -Minitman
> > >
> >
>
>

Another thing, in the formula, enter apr, may, ... and so on till dec.
Also the cell E1 in my formula conatins the text "Jan" and not a date
formatted as MMM.

If it has a date formatted as MMM, then you need to replace the
CHOOSE(MONTH($B$1:$B$7),"Jan","Feb","Mar")=E1
with
MONTH($B$1:$B$7)=E1


Mangesh



"Mangesh Yadav" <mangesh.NOSPAMyadav@gmail.com> wrote in message
news:OJsTbo9bFHA.3120@TK2MSFTNGP12.phx.gbl...
> =SUMPRODUCT(--(CHOOSE(MONTH($B$1:$B$7),"Jan","Feb","Mar")=E1),$DT$1:$DT$7)
>
> confirm with control shift enter
>
> Mangesh
>
>
>
>
>
> "Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
> news:fg5qa11sevd033si63f3q4uiho3cj71aoi@4ax.com...
> > Hey Mangesh,
> >
> > B DT
> > 3 1/20/04 $4.00
> > 4 1/21/04 $3.00
> > 5 1/29/04 $2.00
> > 6 2/14/04 $1.00
> > 7 3/12/04 $11.00
> > 8 3/16/04 $6.00
> > 9 2/22/04 $20.00
> >
> >
> > On a different sheet:
> >
> > Month Amount
> > Jan (Formula goes here, should return $9.00)
> > Feb (Formula goes here, should return $21.00)
> > Mar (Formula goes here, should return $17.00)
> >
> > That is what I am trying to do
> >
> > Any suggestions?
> >
> > -Minitman
> > On Mon, 13 Jun 2005 10:33:55 +0530, "Mangesh Yadav"
> > <m6angesh.NOSPAMyadav@gmail.com> wrote:
> >
> > >Give example of your data and expected answer.
> > >
> > >Mangesh
> > >
> > >
> > >"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
> > >news:4q3qa19nau65sh6e7j57ct6jgltmju59m7@4ax.com...
> > >> Greetings,
> > >>
> > >> I am trying to get the sum for each month in a column which has thee
> > >> entire year.
> > >>
> > >> So far I have:
> > >>
> > >> SUMIF($B:$B,{need month and year here},$DT:$DT)
> > >>
> > >> B is formatted as mm/dd/yy I need mm/yy in the formula. I can't
> > >> remember how to do this! Anyone have any ideas?
> > >>
> > >> Any help would be appreciated.
> > >>
> > >> TIA
> > >>
> > >> -Minitman
> > >
> >
>
>

Another thing, in the formula, enter apr, may, ... and so on till dec.
Also the cell E1 in my formula conatins the text "Jan" and not a date
formatted as MMM.

If it has a date formatted as MMM, then you need to replace the
CHOOSE(MONTH($B$1:$B$7),"Jan","Feb","Mar")=E1
with
MONTH($B$1:$B$7)=E1


Mangesh



"Mangesh Yadav" <mangesh.NOSPAMyadav@gmail.com> wrote in message
news:OJsTbo9bFHA.3120@TK2MSFTNGP12.phx.gbl...
> =SUMPRODUCT(--(CHOOSE(MONTH($B$1:$B$7),"Jan","Feb","Mar")=E1),$DT$1:$DT$7)
>
> confirm with control shift enter
>
> Mangesh
>
>
>
>
>
> "Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
> news:fg5qa11sevd033si63f3q4uiho3cj71aoi@4ax.com...
> > Hey Mangesh,
> >
> > B DT
> > 3 1/20/04 $4.00
> > 4 1/21/04 $3.00
> > 5 1/29/04 $2.00
> > 6 2/14/04 $1.00
> > 7 3/12/04 $11.00
> > 8 3/16/04 $6.00
> > 9 2/22/04 $20.00
> >
> >
> > On a different sheet:
> >
> > Month Amount
> > Jan (Formula goes here, should return $9.00)
> > Feb (Formula goes here, should return $21.00)
> > Mar (Formula goes here, should return $17.00)
> >
> > That is what I am trying to do
> >
> > Any suggestions?
> >
> > -Minitman
> > On Mon, 13 Jun 2005 10:33:55 +0530, "Mangesh Yadav"
> > <m6angesh.NOSPAMyadav@gmail.com> wrote:
> >
> > >Give example of your data and expected answer.
> > >
> > >Mangesh
> > >
> > >
> > >"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
> > >news:4q3qa19nau65sh6e7j57ct6jgltmju59m7@4ax.com...
> > >> Greetings,
> > >>
> > >> I am trying to get the sum for each month in a column which has thee
> > >> entire year.
> > >>
> > >> So far I have:
> > >>
> > >> SUMIF($B:$B,{need month and year here},$DT:$DT)
> > >>
> > >> B is formatted as mm/dd/yy I need mm/yy in the formula. I can't
> > >> remember how to do this! Anyone have any ideas?
> > >>
> > >> Any help would be appreciated.
> > >>
> > >> TIA
> > >>
> > >> -Minitman
> > >
> >
>
>

Sorry, forgot to mention that E1 contain Jan.
Put Feb in E2, and copy down the function to get value for Feb.

Mangesh



"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
news:fg5qa11sevd033si63f3q4uiho3cj71aoi@4ax.com...
> Hey Mangesh,
>
> B DT
> 3 1/20/04 $4.00
> 4 1/21/04 $3.00
> 5 1/29/04 $2.00
> 6 2/14/04 $1.00
> 7 3/12/04 $11.00
> 8 3/16/04 $6.00
> 9 2/22/04 $20.00
>
>
> On a different sheet:
>
> Month Amount
> Jan (Formula goes here, should return $9.00)
> Feb (Formula goes here, should return $21.00)
> Mar (Formula goes here, should return $17.00)
>
> That is what I am trying to do
>
> Any suggestions?
>
> -Minitman
> On Mon, 13 Jun 2005 10:33:55 +0530, "Mangesh Yadav"
> <m6angesh.NOSPAMyadav@gmail.com> wrote:
>
> >Give example of your data and expected answer.
> >
> >Mangesh
> >
> >
> >"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
> >news:4q3qa19nau65sh6e7j57ct6jgltmju59m7@4ax.com...
> >> Greetings,
> >>
> >> I am trying to get the sum for each month in a column which has thee
> >> entire year.
> >>
> >> So far I have:
> >>
> >> SUMIF($B:$B,{need month and year here},$DT:$DT)
> >>
> >> B is formatted as mm/dd/yy I need mm/yy in the formula. I can't
> >> remember how to do this! Anyone have any ideas?
> >>
> >> Any help would be appreciated.
> >>
> >> TIA
> >>
> >> -Minitman
> >
>

Sorry, forgot to mention that E1 contain Jan.
Put Feb in E2, and copy down the function to get value for Feb.

Mangesh



"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
news:fg5qa11sevd033si63f3q4uiho3cj71aoi@4ax.com...
> Hey Mangesh,
>
> B DT
> 3 1/20/04 $4.00
> 4 1/21/04 $3.00
> 5 1/29/04 $2.00
> 6 2/14/04 $1.00
> 7 3/12/04 $11.00
> 8 3/16/04 $6.00
> 9 2/22/04 $20.00
>
>
> On a different sheet:
>
> Month Amount
> Jan (Formula goes here, should return $9.00)
> Feb (Formula goes here, should return $21.00)
> Mar (Formula goes here, should return $17.00)
>
> That is what I am trying to do
>
> Any suggestions?
>
> -Minitman
> On Mon, 13 Jun 2005 10:33:55 +0530, "Mangesh Yadav"
> <m6angesh.NOSPAMyadav@gmail.com> wrote:
>
> >Give example of your data and expected answer.
> >
> >Mangesh
> >
> >
> >"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
> >news:4q3qa19nau65sh6e7j57ct6jgltmju59m7@4ax.com...
> >> Greetings,
> >>
> >> I am trying to get the sum for each month in a column which has thee
> >> entire year.
> >>
> >> So far I have:
> >>
> >> SUMIF($B:$B,{need month and year here},$DT:$DT)
> >>
> >> B is formatted as mm/dd/yy I need mm/yy in the formula. I can't
> >> remember how to do this! Anyone have any ideas?
> >>
> >> Any help would be appreciated.
> >>
> >> TIA
> >>
> >> -Minitman
> >
>

Minitman wrote:
> Hey Mangesh,
>
> B DT
> 3 1/20/04 $4.00
> 4 1/21/04 $3.00
> 5 1/29/04 $2.00
> 6 2/14/04 $1.00
> 7 3/12/04 $11.00
> 8 3/16/04 $6.00
> 9 2/22/04 $20.00
>
>
> On a different sheet:
>
> Month Amount
> Jan (Formula goes here, should return $9.00)
> Feb (Formula goes here, should return $21.00)
> Mar (Formula goes here, should return $17.00)
>
> That is what I am trying to do
>
> Any suggestions?
>
> -Minitman
> On Mon, 13 Jun 2005 10:33:55 +0530, "Mangesh Yadav"
> <m6angesh.NOSPAMyadav@gmail.com> wrote:
>
>
>>Give example of your data and expected answer.
>>
>>Mangesh
>>
>>
>>"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
>>news:4q3qa19nau65sh6e7j57ct6jgltmju59m7@4ax.com...
>>
>>>Greetings,
>>>
>>>I am trying to get the sum for each month in a column which has thee
>>>entire year.
>>>
>>>So far I have:
>>>
>>>SUMIF($B:$B,{need month and year here},$DT:$DT)
>>>
>>>B is formatted as mm/dd/yy I need mm/yy in the formula. I can't
>>>remember how to do this! Anyone have any ideas?
>>>
>>>Any help would be appreciated.
>>>
>>>TIA
>>>
>>>-Minitman
>>
>

Why don't you try the "non-sense" I proposed...

Under Month enter:

1-Jan-04
1-Feb-02
etc.

Format these month cells as mmm-yy.

Supposing that the data on Sheet1 and 1-Jan-04 under Month is in A2,
invoke in B2 faster:

=SUMIF(Sheet1!B:B,">="&A2,Sheet1!DT:DT)-SUMIF(Sheet1!B:B,">"&EOMONTH(A2,0),Sheet1!DT:DT)

and copy down.

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Minitman wrote:
> Hey Mangesh,
>
> B DT
> 3 1/20/04 $4.00
> 4 1/21/04 $3.00
> 5 1/29/04 $2.00
> 6 2/14/04 $1.00
> 7 3/12/04 $11.00
> 8 3/16/04 $6.00
> 9 2/22/04 $20.00
>
>
> On a different sheet:
>
> Month Amount
> Jan (Formula goes here, should return $9.00)
> Feb (Formula goes here, should return $21.00)
> Mar (Formula goes here, should return $17.00)
>
> That is what I am trying to do
>
> Any suggestions?
>
> -Minitman
> On Mon, 13 Jun 2005 10:33:55 +0530, "Mangesh Yadav"
> <m6angesh.NOSPAMyadav@gmail.com> wrote:
>
>
>>Give example of your data and expected answer.
>>
>>Mangesh
>>
>>
>>"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
>>news:4q3qa19nau65sh6e7j57ct6jgltmju59m7@4ax.com...
>>
>>>Greetings,
>>>
>>>I am trying to get the sum for each month in a column which has thee
>>>entire year.
>>>
>>>So far I have:
>>>
>>>SUMIF($B:$B,{need month and year here},$DT:$DT)
>>>
>>>B is formatted as mm/dd/yy I need mm/yy in the formula. I can't
>>>remember how to do this! Anyone have any ideas?
>>>
>>>Any help would be appreciated.
>>>
>>>TIA
>>>
>>>-Minitman
>>
>

Why don't you try the "non-sense" I proposed...

Under Month enter:

1-Jan-04
1-Feb-02
etc.

Format these month cells as mmm-yy.

Supposing that the data on Sheet1 and 1-Jan-04 under Month is in A2,
invoke in B2 faster:

=SUMIF(Sheet1!B:B,">="&A2,Sheet1!DT:DT)-SUMIF(Sheet1!B:B,">"&EOMONTH(A2,0),Sheet1!DT:DT)

and copy down.

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Sorry, I do not understand what is happening with this code.

I did try it and the result is somewhat different then expected. In
my real sheet, the expected total is $181.50 the result with this code
after converting it is $1678.75. Since I do not understand what is
going on, I am not sure where to begin to debug it. Any ideas?

Here is my converted version:

=SUMIF('[Payroll Checks.xls]2003'!$C:$C,">="&A9,'[Payroll
Checks.xls]2003'!$DT:$DT)-SUMIF('[Payroll
Checks.xls]2003'!$C:$C,">"&EOMONTH(A9,0),'[Payroll
Checks.xls]2003'!$DT:$DT)

Payroll Checks is a separate workbook with a sheet called 2003.
A9 is in the workbook of interest instead of A2. C:C is the column of
interest instead of B:B. otherwise I simply cut and paste the formula
into F9 where I need the monthly totals.

Any help would be appreciated.

TIA

-Minitman



On Mon, 13 Jun 2005 08:41:18 +0200, Aladin Akyurek <akyurek@xs4all.nl>
wrote:

>Minitman wrote:
>> Hey Mangesh,
>>
>> B DT
>> 3 1/20/04 $4.00
>> 4 1/21/04 $3.00
>> 5 1/29/04 $2.00
>> 6 2/14/04 $1.00
>> 7 3/12/04 $11.00
>> 8 3/16/04 $6.00
>> 9 2/22/04 $20.00
>>
>>
>> On a different sheet:
>>
>> Month Amount
>> Jan (Formula goes here, should return $9.00)
>> Feb (Formula goes here, should return $21.00)
>> Mar (Formula goes here, should return $17.00)
>>
>> That is what I am trying to do
>>
>> Any suggestions?
>>
>> -Minitman
>> On Mon, 13 Jun 2005 10:33:55 +0530, "Mangesh Yadav"
>> <m6angesh.NOSPAMyadav@gmail.com> wrote:
>>
>>
>>>Give example of your data and expected answer.
>>>
>>>Mangesh
>>>
>>>
>>>"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
>>>news:4q3qa19nau65sh6e7j57ct6jgltmju59m7@4ax.com...
>>>
>>>>Greetings,
>>>>
>>>>I am trying to get the sum for each month in a column which has thee
>>>>entire year.
>>>>
>>>>So far I have:
>>>>
>>>>SUMIF($B:$B,{need month and year here},$DT:$DT)
>>>>
>>>>B is formatted as mm/dd/yy I need mm/yy in the formula. I can't
>>>>remember how to do this! Anyone have any ideas?
>>>>
>>>>Any help would be appreciated.
>>>>
>>>>TIA
>>>>
>>>>-Minitman
>>>
>>
>
>Why don't you try the "non-sense" I proposed...
>
>Under Month enter:
>
>1-Jan-04
>1-Feb-02
>etc.
>
>Format these month cells as mmm-yy.
>
>Supposing that the data on Sheet1 and 1-Jan-04 under Month is in A2,
>invoke in B2 faster:
>
>=SUMIF(Sheet1!B:B,">="&A2,Sheet1!DT:DT)-SUMIF(Sheet1!B:B,">"&EOMONTH(A2,0),Sheet1!DT:DT)
>
>and copy down.

Sorry, I do not understand what is happening with this code.

I did try it and the result is somewhat different then expected. In
my real sheet, the expected total is $181.50 the result with this code
after converting it is $1678.75. Since I do not understand what is
going on, I am not sure where to begin to debug it. Any ideas?

Here is my converted version:

=SUMIF('[Payroll Checks.xls]2003'!$C:$C,">="&A9,'[Payroll
Checks.xls]2003'!$DT:$DT)-SUMIF('[Payroll
Checks.xls]2003'!$C:$C,">"&EOMONTH(A9,0),'[Payroll
Checks.xls]2003'!$DT:$DT)

Payroll Checks is a separate workbook with a sheet called 2003.
A9 is in the workbook of interest instead of A2. C:C is the column of
interest instead of B:B. otherwise I simply cut and paste the formula
into F9 where I need the monthly totals.

Any help would be appreciated.

TIA

-Minitman



On Mon, 13 Jun 2005 08:41:18 +0200, Aladin Akyurek <akyurek@xs4all.nl>
wrote:

>Minitman wrote:
>> Hey Mangesh,
>>
>> B DT
>> 3 1/20/04 $4.00
>> 4 1/21/04 $3.00
>> 5 1/29/04 $2.00
>> 6 2/14/04 $1.00
>> 7 3/12/04 $11.00
>> 8 3/16/04 $6.00
>> 9 2/22/04 $20.00
>>
>>
>> On a different sheet:
>>
>> Month Amount
>> Jan (Formula goes here, should return $9.00)
>> Feb (Formula goes here, should return $21.00)
>> Mar (Formula goes here, should return $17.00)
>>
>> That is what I am trying to do
>>
>> Any suggestions?
>>
>> -Minitman
>> On Mon, 13 Jun 2005 10:33:55 +0530, "Mangesh Yadav"
>> <m6angesh.NOSPAMyadav@gmail.com> wrote:
>>
>>
>>>Give example of your data and expected answer.
>>>
>>>Mangesh
>>>
>>>
>>>"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
>>>news:4q3qa19nau65sh6e7j57ct6jgltmju59m7@4ax.com...
>>>
>>>>Greetings,
>>>>
>>>>I am trying to get the sum for each month in a column which has thee
>>>>entire year.
>>>>
>>>>So far I have:
>>>>
>>>>SUMIF($B:$B,{need month and year here},$DT:$DT)
>>>>
>>>>B is formatted as mm/dd/yy I need mm/yy in the formula. I can't
>>>>remember how to do this! Anyone have any ideas?
>>>>
>>>>Any help would be appreciated.
>>>>
>>>>TIA
>>>>
>>>>-Minitman
>>>
>>
>
>Why don't you try the "non-sense" I proposed...
>
>Under Month enter:
>
>1-Jan-04
>1-Feb-02
>etc.
>
>Format these month cells as mmm-yy.
>
>Supposing that the data on Sheet1 and 1-Jan-04 under Month is in A2,
>invoke in B2 faster:
>
>=SUMIF(Sheet1!B:B,">="&A2,Sheet1!DT:DT)-SUMIF(Sheet1!B:B,">"&EOMONTH(A2,0),Sheet1!DT:DT)
>
>and copy down.

Minitman wrote:
> Sorry, I do not understand what is happening with this code.
>
> I did try it and the result is somewhat different then expected. In
> my real sheet, the expected total is $181.50 the result with this code
> after converting it is $1678.75. Since I do not understand what is
> going on, I am not sure where to begin to debug it. Any ideas?
>
> Here is my converted version:
>
> =SUMIF('[Payroll Checks.xls]2003'!$C:$C,">="&A9,'[Payroll
> Checks.xls]2003'!$DT:$DT)-SUMIF('[Payroll
> Checks.xls]2003'!$C:$C,">"&EOMONTH(A9,0),'[Payroll
> Checks.xls]2003'!$DT:$DT)
>
> Payroll Checks is a separate workbook with a sheet called 2003.
> A9 is in the workbook of interest instead of A2. C:C is the column of
> interest instead of B:B. otherwise I simply cut and paste the formula
> into F9 where I need the monthly totals.
>
> Any help would be appreciated.
>
> TIA
>
> -Minitman
>
>
>
> On Mon, 13 Jun 2005 08:41:18 +0200, Aladin Akyurek <akyurek@xs4all.nl>
> wrote:
>
>
>>Minitman wrote:
>>
>>>Hey Mangesh,
>>>
>>> B DT
>>>3 1/20/04 $4.00
>>>4 1/21/04 $3.00
>>>5 1/29/04 $2.00
>>>6 2/14/04 $1.00
>>>7 3/12/04 $11.00
>>>8 3/16/04 $6.00
>>>9 2/22/04 $20.00
>>>
>>>
>>>On a different sheet:
>>>
>>> Month Amount
>>> Jan (Formula goes here, should return $9.00)
>>> Feb (Formula goes here, should return $21.00)
>>> Mar (Formula goes here, should return $17.00)
>>>
>>>That is what I am trying to do
>>>
>>>Any suggestions?
>>>
>>>-Minitman
>>>On Mon, 13 Jun 2005 10:33:55 +0530, "Mangesh Yadav"
>>><m6angesh.NOSPAMyadav@gmail.com> wrote:
>>>
>>>
>>>
>>>>Give example of your data and expected answer.
>>>>
>>>>Mangesh
>>>>
>>>>
>>>>"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
>>>>news:4q3qa19nau65sh6e7j57ct6jgltmju59m7@4ax.com...
>>>>
>>>>
>>>>>Greetings,
>>>>>
>>>>>I am trying to get the sum for each month in a column which has thee
>>>>>entire year.
>>>>>
>>>>>So far I have:
>>>>>
>>>>>SUMIF($B:$B,{need month and year here},$DT:$DT)
>>>>>
>>>>>B is formatted as mm/dd/yy I need mm/yy in the formula. I can't
>>>>>remember how to do this! Anyone have any ideas?
>>>>>
>>>>>Any help would be appreciated.
>>>>>
>>>>>TIA
>>>>>
>>>>>-Minitman
>>>>
>>Why don't you try the "non-sense" I proposed...
>>
>>Under Month enter:
>>
>>1-Jan-04
>>1-Feb-02
>>etc.
>>
>>Format these month cells as mmm-yy.
>>
>>Supposing that the data on Sheet1 and 1-Jan-04 under Month is in A2,
>>invoke in B2 faster:
>>
>>=SUMIF(Sheet1!B:B,">="&A2,Sheet1!DT:DT)-SUMIF(Sheet1!B:B,">"&EOMONTH(A2,0),Sheet1!DT:DT)
>>
>>and copy down.
>
>

The SumIf formula, that is,...

=SUMIF('[Payroll Checks.xls]2003'!$C:$C,">="&A9,'[Payroll
Checks.xls]2003'!$DT:$DT)-SUMIF('[Payroll
Checks.xls]2003'!$C:$C,">"&EOMONTH(A9,0),'[Payroll Checks.xls]2003'!$DT:$DT)

requires that Payroll Checks.xls is open. The following works also with
that file closed and should produce the same result:

=SUMPRODUCT((DATE(YEAR('[Payroll
Checks.xls]2003'!$C$2:$C$10),MONTH('[Payroll
Checks.xls]2003'!$C$2:$C$10),1)=A9)+0,'[Payroll
Checks.xls]2003'!$DT$2:$DT$10)

Recall that A9 must be a date in the form of 1-Mar-05, that's a first
day date of the month/year of interest.

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Minitman wrote:
> Sorry, I do not understand what is happening with this code.
>
> I did try it and the result is somewhat different then expected. In
> my real sheet, the expected total is $181.50 the result with this code
> after converting it is $1678.75. Since I do not understand what is
> going on, I am not sure where to begin to debug it. Any ideas?
>
> Here is my converted version:
>
> =SUMIF('[Payroll Checks.xls]2003'!$C:$C,">="&A9,'[Payroll
> Checks.xls]2003'!$DT:$DT)-SUMIF('[Payroll
> Checks.xls]2003'!$C:$C,">"&EOMONTH(A9,0),'[Payroll
> Checks.xls]2003'!$DT:$DT)
>
> Payroll Checks is a separate workbook with a sheet called 2003.
> A9 is in the workbook of interest instead of A2. C:C is the column of
> interest instead of B:B. otherwise I simply cut and paste the formula
> into F9 where I need the monthly totals.
>
> Any help would be appreciated.
>
> TIA
>
> -Minitman
>
>
>
> On Mon, 13 Jun 2005 08:41:18 +0200, Aladin Akyurek <akyurek@xs4all.nl>
> wrote:
>
>
>>Minitman wrote:
>>
>>>Hey Mangesh,
>>>
>>> B DT
>>>3 1/20/04 $4.00
>>>4 1/21/04 $3.00
>>>5 1/29/04 $2.00
>>>6 2/14/04 $1.00
>>>7 3/12/04 $11.00
>>>8 3/16/04 $6.00
>>>9 2/22/04 $20.00
>>>
>>>
>>>On a different sheet:
>>>
>>> Month Amount
>>> Jan (Formula goes here, should return $9.00)
>>> Feb (Formula goes here, should return $21.00)
>>> Mar (Formula goes here, should return $17.00)
>>>
>>>That is what I am trying to do
>>>
>>>Any suggestions?
>>>
>>>-Minitman
>>>On Mon, 13 Jun 2005 10:33:55 +0530, "Mangesh Yadav"
>>><m6angesh.NOSPAMyadav@gmail.com> wrote:
>>>
>>>
>>>
>>>>Give example of your data and expected answer.
>>>>
>>>>Mangesh
>>>>
>>>>
>>>>"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
>>>>news:4q3qa19nau65sh6e7j57ct6jgltmju59m7@4ax.com...
>>>>
>>>>
>>>>>Greetings,
>>>>>
>>>>>I am trying to get the sum for each month in a column which has thee
>>>>>entire year.
>>>>>
>>>>>So far I have:
>>>>>
>>>>>SUMIF($B:$B,{need month and year here},$DT:$DT)
>>>>>
>>>>>B is formatted as mm/dd/yy I need mm/yy in the formula. I can't
>>>>>remember how to do this! Anyone have any ideas?
>>>>>
>>>>>Any help would be appreciated.
>>>>>
>>>>>TIA
>>>>>
>>>>>-Minitman
>>>>
>>Why don't you try the "non-sense" I proposed...
>>
>>Under Month enter:
>>
>>1-Jan-04
>>1-Feb-02
>>etc.
>>
>>Format these month cells as mmm-yy.
>>
>>Supposing that the data on Sheet1 and 1-Jan-04 under Month is in A2,
>>invoke in B2 faster:
>>
>>=SUMIF(Sheet1!B:B,">="&A2,Sheet1!DT:DT)-SUMIF(Sheet1!B:B,">"&EOMONTH(A2,0),Sheet1!DT:DT)
>>
>>and copy down.
>
>

The SumIf formula, that is,...

=SUMIF('[Payroll Checks.xls]2003'!$C:$C,">="&A9,'[Payroll
Checks.xls]2003'!$DT:$DT)-SUMIF('[Payroll
Checks.xls]2003'!$C:$C,">"&EOMONTH(A9,0),'[Payroll Checks.xls]2003'!$DT:$DT)

requires that Payroll Checks.xls is open. The following works also with
that file closed and should produce the same result:

=SUMPRODUCT((DATE(YEAR('[Payroll
Checks.xls]2003'!$C$2:$C$10),MONTH('[Payroll
Checks.xls]2003'!$C$2:$C$10),1)=A9)+0,'[Payroll
Checks.xls]2003'!$DT$2:$DT$10)

Recall that A9 must be a date in the form of 1-Mar-05, that's a first
day date of the month/year of interest.

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Minitman wrote:
> Sorry, I do not understand what is happening with this code.
>
> I did try it and the result is somewhat different then expected. In
> my real sheet, the expected total is $181.50 the result with this code
> after converting it is $1678.75. Since I do not understand what is
> going on, I am not sure where to begin to debug it. Any ideas?
>
> Here is my converted version:
>
> =SUMIF('[Payroll Checks.xls]2003'!$C:$C,">="&A9,'[Payroll
> Checks.xls]2003'!$DT:$DT)-SUMIF('[Payroll
> Checks.xls]2003'!$C:$C,">"&EOMONTH(A9,0),'[Payroll
> Checks.xls]2003'!$DT:$DT)
>
> Payroll Checks is a separate workbook with a sheet called 2003.
> A9 is in the workbook of interest instead of A2. C:C is the column of
> interest instead of B:B. otherwise I simply cut and paste the formula
> into F9 where I need the monthly totals.
>
> Any help would be appreciated.
>
> TIA
>
> -Minitman
>
>
>
> On Mon, 13 Jun 2005 08:41:18 +0200, Aladin Akyurek <akyurek@xs4all.nl>
> wrote:
>
>
>>Minitman wrote:
>>
>>>Hey Mangesh,
>>>
>>> B DT
>>>3 1/20/04 $4.00
>>>4 1/21/04 $3.00
>>>5 1/29/04 $2.00
>>>6 2/14/04 $1.00
>>>7 3/12/04 $11.00
>>>8 3/16/04 $6.00
>>>9 2/22/04 $20.00
>>>
>>>
>>>On a different sheet:
>>>
>>> Month Amount
>>> Jan (Formula goes here, should return $9.00)
>>> Feb (Formula goes here, should return $21.00)
>>> Mar (Formula goes here, should return $17.00)
>>>
>>>That is what I am trying to do
>>>
>>>Any suggestions?
>>>
>>>-Minitman
>>>On Mon, 13 Jun 2005 10:33:55 +0530, "Mangesh Yadav"
>>><m6angesh.NOSPAMyadav@gmail.com> wrote:
>>>
>>>
>>>
>>>>Give example of your data and expected answer.
>>>>
>>>>Mangesh
>>>>
>>>>
>>>>"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
>>>>news:4q3qa19nau65sh6e7j57ct6jgltmju59m7@4ax.com...
>>>>
>>>>
>>>>>Greetings,
>>>>>
>>>>>I am trying to get the sum for each month in a column which has thee
>>>>>entire year.
>>>>>
>>>>>So far I have:
>>>>>
>>>>>SUMIF($B:$B,{need month and year here},$DT:$DT)
>>>>>
>>>>>B is formatted as mm/dd/yy I need mm/yy in the formula. I can't
>>>>>remember how to do this! Anyone have any ideas?
>>>>>
>>>>>Any help would be appreciated.
>>>>>
>>>>>TIA
>>>>>
>>>>>-Minitman
>>>>
>>Why don't you try the "non-sense" I proposed...
>>
>>Under Month enter:
>>
>>1-Jan-04
>>1-Feb-02
>>etc.
>>
>>Format these month cells as mmm-yy.
>>
>>Supposing that the data on Sheet1 and 1-Jan-04 under Month is in A2,
>>invoke in B2 faster:
>>
>>=SUMIF(Sheet1!B:B,">="&A2,Sheet1!DT:DT)-SUMIF(Sheet1!B:B,">"&EOMONTH(A2,0),Sheet1!DT:DT)
>>
>>and copy down.
>
>

The SumIf formula, that is,...

=SUMIF('[Payroll Checks.xls]2003'!$C:$C,">="&A9,'[Payroll
Checks.xls]2003'!$DT:$DT)-SUMIF('[Payroll
Checks.xls]2003'!$C:$C,">"&EOMONTH(A9,0),'[Payroll Checks.xls]2003'!$DT:$DT)

requires that Payroll Checks.xls is open. The following works also with
that file closed and should produce the same result:

=SUMPRODUCT((DATE(YEAR('[Payroll
Checks.xls]2003'!$C$2:$C$10),MONTH('[Payroll
Checks.xls]2003'!$C$2:$C$10),1)=A9)+0,'[Payroll
Checks.xls]2003'!$DT$2:$DT$10)

Recall that A9 must be a date in the form of 1-Mar-05, that's a first
day date of the month/year of interest.

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Sorry, I do not understand what is happening with this code.

I did try it and the result is somewhat different then expected. In
my real sheet, the expected total is $181.50 the result with this code
after converting it is $1678.75. Since I do not understand what is
going on, I am not sure where to begin to debug it. Any ideas?

Here is my converted version:

=SUMIF('[Payroll Checks.xls]2003'!$C:$C,">="&A9,'[Payroll
Checks.xls]2003'!$DT:$DT)-SUMIF('[Payroll
Checks.xls]2003'!$C:$C,">"&EOMONTH(A9,0),'[Payroll
Checks.xls]2003'!$DT:$DT)

Payroll Checks is a separate workbook with a sheet called 2003.
A9 is in the workbook of interest instead of A2. C:C is the column of
interest instead of B:B. otherwise I simply cut and paste the formula
into F9 where I need the monthly totals.

Any help would be appreciated.

TIA

-Minitman



On Mon, 13 Jun 2005 08:41:18 +0200, Aladin Akyurek <akyurek@xs4all.nl>
wrote:

>Minitman wrote:
>> Hey Mangesh,
>>
>> B DT
>> 3 1/20/04 $4.00
>> 4 1/21/04 $3.00
>> 5 1/29/04 $2.00
>> 6 2/14/04 $1.00
>> 7 3/12/04 $11.00
>> 8 3/16/04 $6.00
>> 9 2/22/04 $20.00
>>
>>
>> On a different sheet:
>>
>> Month Amount
>> Jan (Formula goes here, should return $9.00)
>> Feb (Formula goes here, should return $21.00)
>> Mar (Formula goes here, should return $17.00)
>>
>> That is what I am trying to do
>>
>> Any suggestions?
>>
>> -Minitman
>> On Mon, 13 Jun 2005 10:33:55 +0530, "Mangesh Yadav"
>> <m6angesh.NOSPAMyadav@gmail.com> wrote:
>>
>>
>>>Give example of your data and expected answer.
>>>
>>>Mangesh
>>>
>>>
>>>"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
>>>news:4q3qa19nau65sh6e7j57ct6jgltmju59m7@4ax.com...
>>>
>>>>Greetings,
>>>>
>>>>I am trying to get the sum for each month in a column which has thee
>>>>entire year.
>>>>
>>>>So far I have:
>>>>
>>>>SUMIF($B:$B,{need month and year here},$DT:$DT)
>>>>
>>>>B is formatted as mm/dd/yy I need mm/yy in the formula. I can't
>>>>remember how to do this! Anyone have any ideas?
>>>>
>>>>Any help would be appreciated.
>>>>
>>>>TIA
>>>>
>>>>-Minitman
>>>
>>
>
>Why don't you try the "non-sense" I proposed...
>
>Under Month enter:
>
>1-Jan-04
>1-Feb-02
>etc.
>
>Format these month cells as mmm-yy.
>
>Supposing that the data on Sheet1 and 1-Jan-04 under Month is in A2,
>invoke in B2 faster:
>
>=SUMIF(Sheet1!B:B,">="&A2,Sheet1!DT:DT)-SUMIF(Sheet1!B:B,">"&EOMONTH(A2,0),Sheet1!DT:DT)
>
>and copy down.

=SUMPRODUCT(--(CHOOSE(MONTH($B$1:$B$7),"Jan","Feb","Mar")=E1),$DT$1:$DT$7)

confirm with control shift enter

Mangesh





"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
news:fg5qa11sevd033si63f3q4uiho3cj71aoi@4ax.com...
> Hey Mangesh,
>
> B DT
> 3 1/20/04 $4.00
> 4 1/21/04 $3.00
> 5 1/29/04 $2.00
> 6 2/14/04 $1.00
> 7 3/12/04 $11.00
> 8 3/16/04 $6.00
> 9 2/22/04 $20.00
>
>
> On a different sheet:
>
> Month Amount
> Jan (Formula goes here, should return $9.00)
> Feb (Formula goes here, should return $21.00)
> Mar (Formula goes here, should return $17.00)
>
> That is what I am trying to do
>
> Any suggestions?
>
> -Minitman
> On Mon, 13 Jun 2005 10:33:55 +0530, "Mangesh Yadav"
> <m6angesh.NOSPAMyadav@gmail.com> wrote:
>
> >Give example of your data and expected answer.
> >
> >Mangesh
> >
> >
> >"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
> >news:4q3qa19nau65sh6e7j57ct6jgltmju59m7@4ax.com...
> >> Greetings,
> >>
> >> I am trying to get the sum for each month in a column which has thee
> >> entire year.
> >>
> >> So far I have:
> >>
> >> SUMIF($B:$B,{need month and year here},$DT:$DT)
> >>
> >> B is formatted as mm/dd/yy I need mm/yy in the formula. I can't
> >> remember how to do this! Anyone have any ideas?
> >>
> >> Any help would be appreciated.
> >>
> >> TIA
> >>
> >> -Minitman
> >
>

Sorry, forgot to mention that E1 contain Jan.
Put Feb in E2, and copy down the function to get value for Feb.

Mangesh



"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
news:fg5qa11sevd033si63f3q4uiho3cj71aoi@4ax.com...
> Hey Mangesh,
>
> B DT
> 3 1/20/04 $4.00
> 4 1/21/04 $3.00
> 5 1/29/04 $2.00
> 6 2/14/04 $1.00
> 7 3/12/04 $11.00
> 8 3/16/04 $6.00
> 9 2/22/04 $20.00
>
>
> On a different sheet:
>
> Month Amount
> Jan (Formula goes here, should return $9.00)
> Feb (Formula goes here, should return $21.00)
> Mar (Formula goes here, should return $17.00)
>
> That is what I am trying to do
>
> Any suggestions?
>
> -Minitman
> On Mon, 13 Jun 2005 10:33:55 +0530, "Mangesh Yadav"
> <m6angesh.NOSPAMyadav@gmail.com> wrote:
>
> >Give example of your data and expected answer.
> >
> >Mangesh
> >
> >
> >"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
> >news:4q3qa19nau65sh6e7j57ct6jgltmju59m7@4ax.com...
> >> Greetings,
> >>
> >> I am trying to get the sum for each month in a column which has thee
> >> entire year.
> >>
> >> So far I have:
> >>
> >> SUMIF($B:$B,{need month and year here},$DT:$DT)
> >>
> >> B is formatted as mm/dd/yy I need mm/yy in the formula. I can't
> >> remember how to do this! Anyone have any ideas?
> >>
> >> Any help would be appreciated.
> >>
> >> TIA
> >>
> >> -Minitman
> >
>

Minitman wrote:
> Hey Mangesh,
>
> B DT
> 3 1/20/04 $4.00
> 4 1/21/04 $3.00
> 5 1/29/04 $2.00
> 6 2/14/04 $1.00
> 7 3/12/04 $11.00
> 8 3/16/04 $6.00
> 9 2/22/04 $20.00
>
>
> On a different sheet:
>
> Month Amount
> Jan (Formula goes here, should return $9.00)
> Feb (Formula goes here, should return $21.00)
> Mar (Formula goes here, should return $17.00)
>
> That is what I am trying to do
>
> Any suggestions?
>
> -Minitman
> On Mon, 13 Jun 2005 10:33:55 +0530, "Mangesh Yadav"
> <m6angesh.NOSPAMyadav@gmail.com> wrote:
>
>
>>Give example of your data and expected answer.
>>
>>Mangesh
>>
>>
>>"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
>>news:4q3qa19nau65sh6e7j57ct6jgltmju59m7@4ax.com...
>>
>>>Greetings,
>>>
>>>I am trying to get the sum for each month in a column which has thee
>>>entire year.
>>>
>>>So far I have:
>>>
>>>SUMIF($B:$B,{need month and year here},$DT:$DT)
>>>
>>>B is formatted as mm/dd/yy I need mm/yy in the formula. I can't
>>>remember how to do this! Anyone have any ideas?
>>>
>>>Any help would be appreciated.
>>>
>>>TIA
>>>
>>>-Minitman
>>
>

Why don't you try the "non-sense" I proposed...

Under Month enter:

1-Jan-04
1-Feb-02
etc.

Format these month cells as mmm-yy.

Supposing that the data on Sheet1 and 1-Jan-04 under Month is in A2,
invoke in B2 faster:

=SUMIF(Sheet1!B:B,">="&A2,Sheet1!DT:DT)-SUMIF(Sheet1!B:B,">"&EOMONTH(A2,0),Sheet1!DT:DT)

and copy down.

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Hey Mangesh,

B DT
3 1/20/04 $4.00
4 1/21/04 $3.00
5 1/29/04 $2.00
6 2/14/04 $1.00
7 3/12/04 $11.00
8 3/16/04 $6.00
9 2/22/04 $20.00


On a different sheet:

Month Amount
Jan (Formula goes here, should return $9.00)
Feb (Formula goes here, should return $21.00)
Mar (Formula goes here, should return $17.00)

That is what I am trying to do

Any suggestions?

-Minitman
On Mon, 13 Jun 2005 10:33:55 +0530, "Mangesh Yadav"
<m6angesh.NOSPAMyadav@gmail.com> wrote:

>Give example of your data and expected answer.
>
>Mangesh
>
>
>"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
>news:4q3qa19nau65sh6e7j57ct6jgltmju59m7@4ax.com...
>> Greetings,
>>
>> I am trying to get the sum for each month in a column which has thee
>> entire year.
>>
>> So far I have:
>>
>> SUMIF($B:$B,{need month and year here},$DT:$DT)
>>
>> B is formatted as mm/dd/yy I need mm/yy in the formula. I can't
>> remember how to do this! Anyone have any ideas?
>>
>> Any help would be appreciated.
>>
>> TIA
>>
>> -Minitman
>

Minitman wrote:
> Greetings,
>
> I am trying to get the sum for each month in a column which has thee
> entire year.
>
> So far I have:
>
> SUMIF($B:$B,{need month and year here},$DT:$DT)
>
> B is formatted as mm/dd/yy I need mm/yy in the formula. I can't
> remember how to do this! Anyone have any ideas?
>
> Any help would be appreciated.
>
> TIA
>
> -Minitman

=SUMIF(B:B,">="&A2,DT:DT)-SUMIF(B:B,">"&A3,DT:DT)

where A2 is a first day date of the mont/year of interest like 1-Mar-04
and A3 houses: =EOMONTH(A2,0).

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Give example of your data and expected answer.

Mangesh


"Minitman" <exreply@i-m-pNOSPAM.net> wrote in message
news:4q3qa19nau65sh6e7j57ct6jgltmju59m7@4ax.com...
> Greetings,
>
> I am trying to get the sum for each month in a column which has thee
> entire year.
>
> So far I have:
>
> SUMIF($B:$B,{need month and year here},$DT:$DT)
>
> B is formatted as mm/dd/yy I need mm/yy in the formula. I can't
> remember how to do this! Anyone have any ideas?
>
> Any help would be appreciated.
>
> TIA
>
> -Minitman