monthly total

Turk · Sep 4, 2005

Dear all,

I have a worksheet as below

5/1/2005 50
23/1/2005 100
18/1/2005 120
2/2/2005 12
12/2/2005 24
4/3/2005 15

I want to sum up the total of each month to get

Jan 2005 270
Feb 2005 36
Mar 2005 15

What should be the formula?
thanks....

ims

KL · Sep 4, 2005

Hi Turk,

If all data belong to the same year you could try the following formula to
get the sum for January:

=SUMPRODUCT(--(MONTH($A$1:$A$6)=1),$B$1:$B$6)

otherwise:

=SUMPRODUCT(--(MONTH($A$1:$A$6)=1),--(YEAR($A$1:$A$6)=2005),$B$1:$B$6)

or

=SUMPRODUCT(--(TEXT($A$1:$A$6,"mmyy")="0105"),$B$1:$B$6)

etc.

You can also replace =1 with a reference to the cell that contains the
number of the month, e.g.

=SUMPRODUCT(--(MONTH($A$1:$A$6)=D1),$B$1:$B$6)

Regards,
KL

Aladin Akyurek · Sep 4, 2005

Let A2:B7 house the sample you provided.

In column C from C2 on, enter the first day dates of the month/year
combinations:

1-Jan-2005
1-Feb-2005

etc.

In D2 enter & copy down:

=SUMIF($A$2:$A$7,">="&C2,$B$2:$B$7)-SUMIF($A$2:$A$7,">"&EOMONTH(C2,0),$B$2:$B$7)

Dear all,

I have a worksheet as below

5/1/2005 50
23/1/2005 100
18/1/2005 120
2/2/2005 12
12/2/2005 24
4/3/2005 15

I want to sum up the total of each month to get

Jan 2005 270
Feb 2005 36
Mar 2005 15

What should be the formula?
thanks....

ims

--

[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.

Turk · Sep 4, 2005

Not in the same year in my case.

KL said:
Hi Turk,

If all data belong to the same year you could try the following formula to
get the sum for January:

=SUMPRODUCT(--(MONTH($A$1:$A$6)=1),$B$1:$B$6)

otherwise:

=SUMPRODUCT(--(MONTH($A$1:$A$6)=1),--(YEAR($A$1:$A$6)=2005),$B$1:$B$6)

or

=SUMPRODUCT(--(TEXT($A$1:$A$6,"mmyy")="0105"),$B$1:$B$6)

etc.

You can also replace =1 with a reference to the cell that contains the
number of the month, e.g.

=SUMPRODUCT(--(MONTH($A$1:$A$6)=D1),$B$1:$B$6)

Regards,
KL

Turk · Sep 4, 2005

The formula seems has some problems, can only get correct results for May
and June as below.

A B C D
2005/3/1 12 2005/1/1 193
2005/3/4 24 2005/2/1 193
2005/3/17 36 2005/3/1 193
2005/4/2 25 2005/4/1 121
2005/4/6 50 2005/5/1 46
2005/5/8 46 2005/6/1 0

Aladin Akyurek said:
Let A2:B7 house the sample you provided.

In column C from C2 on, enter the first day dates of the month/year
combinations:

1-Jan-2005
1-Feb-2005

etc.

In D2 enter & copy down:

=SUMIF($A$2:$A$7,">="&C2,$B$2:$B$7)-SUMIF($A$2:$A$7,">"&EOMONTH(C2,0),$B$2:$
B$7)

Dear all,

I have a worksheet as below

5/1/2005 50
23/1/2005 100
18/1/2005 120
2/2/2005 12
12/2/2005 24
4/3/2005 15

I want to sum up the total of each month to get

Jan 2005 270
Feb 2005 36
Mar 2005 15

What should be the formula?
thanks....

ims

Click to expand...

--

[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.

Turk · Sep 4, 2005

The second formula is the one I wanted, thanks a lot~

Don Guillett · Sep 4, 2005

To add a bit instead of editing for 1, 2, 3 you could have used row(a1) and
copied down

Turk · Sep 4, 2005

I have changed my mind to use the 3rd formula, with little modification.

By entering 2005-01 at H1 and coping down the column,

=SUMPRODUCT(--(TEXT($A$1:$A$6,"yyyy-mm")=H1),$B$1:$B$6)

So user friendly! Thanks again......

Aladin Akyurek · Sep 4, 2005

Make sure that the dates are true dates, not just looking as dates. A
diganostic test would be:

=COUNT(DateRange)=SUMPRODUCT(ISNUMBER(DateRange+0)+0)

If the result is not TRUE, then some or all of your dates are
text-formatted.

Turk said:
The formula seems has some problems, can only get correct results for May
and June as below.

A B C D
2005/3/1 12 2005/1/1 193
2005/3/4 24 2005/2/1 193
2005/3/17 36 2005/3/1 193
2005/4/2 25 2005/4/1 121
2005/4/6 50 2005/5/1 46
2005/5/8 46 2005/6/1 0

Let A2:B7 house the sample you provided.

In column C from C2 on, enter the first day dates of the month/year
combinations:

1-Jan-2005
1-Feb-2005

etc.

In D2 enter & copy down:

=SUMIF($A$2:$A$7,">="&C2,$B$2:$B$7)-SUMIF($A$2:$A$7,">"&EOMONTH(C2,0),$B$2:$
B$7)

Turk said:

Dear all,

I have a worksheet as below

5/1/2005 50
23/1/2005 100
18/1/2005 120
2/2/2005 12
12/2/2005 24
4/3/2005 15

I want to sum up the total of each month to get

Jan 2005 270
Feb 2005 36
Mar 2005 15

What should be the formula?
thanks....

ims

Click to expand...

--

[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.

Click to expand...

--

[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.

Harlan Grove · Sep 5, 2005

Make sure that the dates are true dates, not just looking as dates. A
diganostic test would be:

=COUNT(DateRange)=SUMPRODUCT(ISNUMBER(DateRange+0)+0)

....

If we're talking dates pasted from HTML tables with leading or trailing
nonbreaking spaces (decimal char code 160), adding 0 to them will return
errors, in which case your COUNT and SUMPRODUCT calls would return the same
values.

More robust to use the array formula

=COUNT(DataRange)=COUNT(-SUBSTITUTE(DateRange,CHAR(160),""))

Don Guillett · Sep 5, 2005

If you like it, that's great but you really don't need to add a col...

Turk · Sep 6, 2005

Yeah~
SOme of them are in text format, thanks!

Aladin Akyurek said:
Make sure that the dates are true dates, not just looking as dates. A
diganostic test would be:

=COUNT(DateRange)=SUMPRODUCT(ISNUMBER(DateRange+0)+0)

If the result is not TRUE, then some or all of your dates are
text-formatted.

The formula seems has some problems, can only get correct results for May
and June as below.

A B C D
2005/3/1 12 2005/1/1 193
2005/3/4 24 2005/2/1 193
2005/3/17 36 2005/3/1 193
2005/4/2 25 2005/4/1 121
2005/4/6 50 2005/5/1 46
2005/5/8 46 2005/6/1 0

Let A2:B7 house the sample you provided.

In column C from C2 on, enter the first day dates of the month/year
combinations:

1-Jan-2005
1-Feb-2005

etc.

In D2 enter & copy down:

Click to expand...

=SUMIF($A$2:$A$7,">="&C2,$B$2:$B$7)-SUMIF($A$2:$A$7,">"&EOMONTH(C2,0),$B$2:$
B$7)

Turk wrote:

Dear all,

I have a worksheet as below

5/1/2005 50
23/1/2005 100
18/1/2005 120
2/2/2005 12
12/2/2005 24
4/3/2005 15

I want to sum up the total of each month to get

Jan 2005 270
Feb 2005 36
Mar 2005 15

What should be the formula?
thanks....

ims

--

[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.

Click to expand...

Click to expand...

--

[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.

Aladin Akyurek · Sep 7, 2005

Convert them to true dates. SumIf formula (which is faster its
SumProduct equivalent) will work as advertised.

Yeah~
SOme of them are in text format, thanks!

Make sure that the dates are true dates, not just looking as dates. A
diganostic test would be:

=COUNT(DateRange)=SUMPRODUCT(ISNUMBER(DateRange+0)+0)

If the result is not TRUE, then some or all of your dates are
text-formatted.

The formula seems has some problems, can only get correct results for

Click to expand...

May

and June as below.

A B C D
2005/3/1 12 2005/1/1 193
2005/3/4 24 2005/2/1 193
2005/3/17 36 2005/3/1 193
2005/4/2 25 2005/4/1 121
2005/4/6 50 2005/5/1 46
2005/5/8 46 2005/6/1 0

"Aladin Akyurek" <[email protected]>
???????:[email protected]...

Let A2:B7 house the sample you provided.

In column C from C2 on, enter the first day dates of the month/year
combinations:

1-Jan-2005
1-Feb-2005

etc.

In D2 enter & copy down:

Click to expand...

=SUMIF($A$2:$A$7,">="&C2,$B$2:$B$7)-SUMIF($A$2:$A$7,">"&EOMONTH(C2,0),$B$2:$

B$7)

Turk wrote:

Dear all,

I have a worksheet as below

5/1/2005 50
23/1/2005 100
18/1/2005 120
2/2/2005 12
12/2/2005 24
4/3/2005 15

I want to sum up the total of each month to get

Jan 2005 270
Feb 2005 36
Mar 2005 15

What should be the formula?
thanks....

ims

--

[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.

Click to expand...

--

[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.

Click to expand...

--

[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.

monthly total

Turk

KL

Aladin Akyurek

Turk

Turk

Turk

Don Guillett

Turk

Aladin Akyurek

Harlan Grove

Don Guillett

Turk

Aladin Akyurek