LinkBack URL
About LinkBacksMinitman 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.
Register To Reply
Bookmarks