monthly total

  • Thread starter Thread starter Turk
  • Start date Start date
T

Turk

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
 
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
 
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.
 
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
Click to expand...
 
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.
Click to expand...
 
To add a bit instead of editing for 1, 2, 3 you could have used row(a1) and
copied down
 
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......
 
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:


=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...
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.
 
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)
Click to expand...
....

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),""))
 
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.
Click to expand...
 
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...
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.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pivot Table GETPIVOTDATA 2
Date Sorting 2
Pivot from multiple sheets 1
How can I find the greatest possible sum within 12 months? A newbie... 22
NetWorkdays Calculation isn't working 6
WCG Stats Saturday 30 September 2023 3
rEPOST: Summing clock in and out by date 4
Summing clock in and out by date 1

Back
Top