Date Format Inside of a SUMIF Statement

[Minitman]

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

 

[Aladin Akyurek]

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,DTT)-SUMIF(B:B,">"&A3,DTT)

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.

 

[Mangesh Yadav]

Give example of your data and expected answer.

Mangesh

 

[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

 

[Mangesh Yadav]

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

confirm with control shift enter

Mangesh

 

[Mangesh Yadav]

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

Mangesh

 

[Mangesh Yadav]

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

 

[Aladin Akyurek]

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

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!DTT)-SUMIF(Sheet1!B:B,">"&EOMONTH(A2,0),Sheet1!DTT)

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]

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

 

[Aladin Akyurek]

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

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!DTT)-SUMIF(Sheet1!B:B,">"&EOMONTH(A2,0),Sheet1!DTT)

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.