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.