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" wrote in message
...
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"
wrote:

Give example of your data and expected answer.

Mangesh


"Minitman" wrote in message
.. .
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),$D T$1:$DT$7)

confirm with control shift enter

Mangesh





"Minitman" wrote in message
...
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"
wrote:

Give example of your data and expected answer.

Mangesh


"Minitman" wrote in message
.. .
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" wrote in message
...
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"
wrote:

Give example of your data and expected answer.

Mangesh


"Minitman" wrote in message
.. .
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" wrote in message
...
=SUMPRODUCT(--(CHOOSE(MONTH($B$1:$B$7),"Jan","Feb","Mar")=E1),$D T$1:$DT$7)

confirm with control shift enter

Mangesh





"Minitman" wrote in message
...
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"
wrote:

Give example of your data and expected answer.

Mangesh


"Minitman" wrote in message
.. .
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"
wrote:


Give example of your data and expected answer.

Mangesh


"Minitman" wrote in message
. ..

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
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"
wrote:


Give example of your data and expected answer.

Mangesh


"Minitman" wrote in message
...

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
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"
wrote:



Give example of your data and expected answer.

Mangesh


"Minitman" wrote in message
m...


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.