Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Chris
 
Posts: n/a
Default Using the Month function to sum another column

I have a column with dates and another with totals. I want to for every month
(01 - 12) to search all the rows in columnA and sum the totals only relevent
to the month from columnB. E.g for OCtober the total is 1500
ColumnA columnB
10/10/04 1000
10/10/04 500
10/09/04 250
  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

Hi
try
=SUMPRODUCT(--(MONTH(A1:A100=10),--(YEAR(A1:A100=2004),B1:B100)

--
Regards
Frank Kabel
Frankfurt, Germany

"Chris" schrieb im Newsbeitrag
...
I have a column with dates and another with totals. I want to for

every month
(01 - 12) to search all the rows in columnA and sum the totals only

relevent
to the month from columnB. E.g for OCtober the total is 1500
ColumnA columnB
10/10/04 1000
10/10/04 500
10/09/04 250


  #3   Report Post  
Chris
 
Posts: n/a
Default

Hi Frank
this gives me an error. I've changed the A1:100 ranges to my one. Any ideas.
Using 2002 version. Also what do the -- mean

Cherers
Chris

"Frank Kabel" wrote:

Hi
try
=SUMPRODUCT(--(MONTH(A1:A100=10),--(YEAR(A1:A100=2004),B1:B100)

--
Regards
Frank Kabel
Frankfurt, Germany

"Chris" schrieb im Newsbeitrag
...
I have a column with dates and another with totals. I want to for

every month
(01 - 12) to search all the rows in columnA and sum the totals only

relevent
to the month from columnB. E.g for OCtober the total is 1500
ColumnA columnB
10/10/04 1000
10/10/04 500
10/09/04 250



  #4   Report Post  
Frank Kabel
 
Posts: n/a
Default

Hi
what is the formula you have used and what exact error do you get.
Note: you can't use a range A:A together with SUMPRODUCT.

For an explanation see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html


--
Regards
Frank Kabel
Frankfurt, Germany


Chris wrote:
Hi Frank
this gives me an error. I've changed the A1:100 ranges to my one. Any
ideas. Using 2002 version. Also what do the -- mean

Cherers
Chris

"Frank Kabel" wrote:

Hi
try
=SUMPRODUCT(--(MONTH(A1:A100=10),--(YEAR(A1:A100=2004),B1:B100)

--
Regards
Frank Kabel
Frankfurt, Germany

"Chris" schrieb im Newsbeitrag
...
I have a column with dates and another with totals. I want to for

every month
(01 - 12) to search all the rows in columnA and sum the totals only
relevent to the month from columnB. E.g for OCtober the total is
1500 ColumnA columnB
10/10/04 1000
10/10/04 500
10/09/04 250


  #5   Report Post  
Chris
 
Posts: n/a
Default

For my sins I used what you said below (I am a novice at this) I used a
range. I need to total all the amounts that have a month of say 04 or 10
associated. The error I get is the standard formula error. If that makes
sense?

"Frank Kabel" wrote:

Hi
what is the formula you have used and what exact error do you get.
Note: you can't use a range A:A together with SUMPRODUCT.

For an explanation see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html


--
Regards
Frank Kabel
Frankfurt, Germany


Chris wrote:
Hi Frank
this gives me an error. I've changed the A1:100 ranges to my one. Any
ideas. Using 2002 version. Also what do the -- mean

Cherers
Chris

"Frank Kabel" wrote:

Hi
try
=SUMPRODUCT(--(MONTH(A1:A100=10),--(YEAR(A1:A100=2004),B1:B100)

--
Regards
Frank Kabel
Frankfurt, Germany

"Chris" schrieb im Newsbeitrag
...
I have a column with dates and another with totals. I want to for
every month
(01 - 12) to search all the rows in columnA and sum the totals only
relevent to the month from columnB. E.g for OCtober the total is
1500 ColumnA columnB
10/10/04 1000
10/10/04 500
10/09/04 250





  #6   Report Post  
Frank Kabel
 
Posts: n/a
Default

Hi
just post the formula you have tried

--
Regards
Frank Kabel
Frankfurt, Germany


Chris wrote:
For my sins I used what you said below (I am a novice at this) I used
a range. I need to total all the amounts that have a month of say 04
or 10 associated. The error I get is the standard formula error. If
that makes sense?

"Frank Kabel" wrote:

Hi
what is the formula you have used and what exact error do you get.
Note: you can't use a range A:A together with SUMPRODUCT.

For an explanation see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html


--
Regards
Frank Kabel
Frankfurt, Germany


Chris wrote:
Hi Frank
this gives me an error. I've changed the A1:100 ranges to my one.
Any ideas. Using 2002 version. Also what do the -- mean

Cherers
Chris

"Frank Kabel" wrote:

Hi
try
=SUMPRODUCT(--(MONTH(A1:A100=10),--(YEAR(A1:A100=2004),B1:B100)

--
Regards
Frank Kabel
Frankfurt, Germany

"Chris" schrieb im Newsbeitrag
...
I have a column with dates and another with totals. I want to for
every month (01 - 12) to search all the rows in columnA and sum
the totals only relevent to the month from columnB. E.g for
OCtober the total is 1500 ColumnA columnB
10/10/04 1000
10/10/04 500
10/09/04 250


  #7   Report Post  
Chris
 
Posts: n/a
Default

Here you go
=SUMPRODUCT(--(MONTH(C4:C6=10),--(YEAR(C4:C6=2004),E4:E6)
COlumn C has the dates formated as date mm/dd/yy and column E has the
amounts, formatted as currency £

Cheers

"Frank Kabel" wrote:

Hi
just post the formula you have tried

--
Regards
Frank Kabel
Frankfurt, Germany


Chris wrote:
For my sins I used what you said below (I am a novice at this) I used
a range. I need to total all the amounts that have a month of say 04
or 10 associated. The error I get is the standard formula error. If
that makes sense?

"Frank Kabel" wrote:

Hi
what is the formula you have used and what exact error do you get.
Note: you can't use a range A:A together with SUMPRODUCT.

For an explanation see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html


--
Regards
Frank Kabel
Frankfurt, Germany


Chris wrote:
Hi Frank
this gives me an error. I've changed the A1:100 ranges to my one.
Any ideas. Using 2002 version. Also what do the -- mean

Cherers
Chris

"Frank Kabel" wrote:

Hi
try
=SUMPRODUCT(--(MONTH(A1:A100=10),--(YEAR(A1:A100=2004),B1:B100)

--
Regards
Frank Kabel
Frankfurt, Germany

"Chris" schrieb im Newsbeitrag
...
I have a column with dates and another with totals. I want to for
every month (01 - 12) to search all the rows in columnA and sum
the totals only relevent to the month from columnB. E.g for
OCtober the total is 1500 ColumnA columnB
10/10/04 1000
10/10/04 500
10/09/04 250



  #8   Report Post  
Myrna Larson
 
Posts: n/a
Default

Hi, Frank:

Maybe the problems is with missing parentheses:

=SUMPRODUCT(--(MONTH(A1:A100)=10),--(YEAR(A1:A100)=2004),B1:B100)

On Fri, 29 Oct 2004 18:16:05 +0200, "Frank Kabel"
wrote:

Hi
try
=SUMPRODUCT(--(MONTH(A1:A100=10),--(YEAR(A1:A100=2004),B1:B100)


  #9   Report Post  
Frank Kabel
 
Posts: n/a
Default

Hi
try:
=SUMPRODUCT(--(MONTH(C4:C6)=10),--(YEAR(C4:C6)=2004),E4:E6)

--
Regards
Frank Kabel
Frankfurt, Germany


Chris wrote:
Here you go
=SUMPRODUCT(--(MONTH(C4:C6=10),--(YEAR(C4:C6=2004),E4:E6)
COlumn C has the dates formated as date mm/dd/yy and column E has the
amounts, formatted as currency £

Cheers

"Frank Kabel" wrote:

Hi
just post the formula you have tried

--
Regards
Frank Kabel
Frankfurt, Germany


Chris wrote:
For my sins I used what you said below (I am a novice at this) I
used a range. I need to total all the amounts that have a month of
say 04 or 10 associated. The error I get is the standard formula
error. If that makes sense?

"Frank Kabel" wrote:

Hi
what is the formula you have used and what exact error do you get.
Note: you can't use a range A:A together with SUMPRODUCT.

For an explanation see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html


--
Regards
Frank Kabel
Frankfurt, Germany


Chris wrote:
Hi Frank
this gives me an error. I've changed the A1:100 ranges to my one.
Any ideas. Using 2002 version. Also what do the -- mean

Cherers
Chris

"Frank Kabel" wrote:

Hi
try
=SUMPRODUCT(--(MONTH(A1:A100=10),--(YEAR(A1:A100=2004),B1:B100)

--
Regards
Frank Kabel
Frankfurt, Germany

"Chris" schrieb im Newsbeitrag
...
I have a column with dates and another with totals. I want to
for every month (01 - 12) to search all the rows in columnA and
sum the totals only relevent to the month from columnB. E.g for
OCtober the total is 1500 ColumnA columnB
10/10/04 1000
10/10/04 500
10/09/04 250


  #10   Report Post  
Frank Kabel
 
Posts: n/a
Default

Hi
:-)
thought so after seeing the formula the OP tried

Sorry for my typos


--
Regards
Frank Kabel
Frankfurt, Germany


Myrna Larson wrote:
Hi, Frank:

Maybe the problems is with missing parentheses:

=SUMPRODUCT(--(MONTH(A1:A100)=10),--(YEAR(A1:A100)=2004),B1:B100)

On Fri, 29 Oct 2004 18:16:05 +0200, "Frank Kabel"
wrote:

Hi
try
=SUMPRODUCT(--(MONTH(A1:A100=10),--(YEAR(A1:A100=2004),B1:B100)



  #11   Report Post  
Chris
 
Posts: n/a
Default

Nice one

Many thanks

"Myrna Larson" wrote:

Hi, Frank:

Maybe the problems is with missing parentheses:

=SUMPRODUCT(--(MONTH(A1:A100)=10),--(YEAR(A1:A100)=2004),B1:B100)

On Fri, 29 Oct 2004 18:16:05 +0200, "Frank Kabel"
wrote:

Hi
try
=SUMPRODUCT(--(MONTH(A1:A100=10),--(YEAR(A1:A100=2004),B1:B100)



  #12   Report Post  
Chris
 
Posts: n/a
Default

One last thing, can I get this to go down a column until it meets a blank
cell rather than specify a range?

"Frank Kabel" wrote:

Hi
:-)
thought so after seeing the formula the OP tried

Sorry for my typos


--
Regards
Frank Kabel
Frankfurt, Germany


Myrna Larson wrote:
Hi, Frank:

Maybe the problems is with missing parentheses:

=SUMPRODUCT(--(MONTH(A1:A100)=10),--(YEAR(A1:A100)=2004),B1:B100)

On Fri, 29 Oct 2004 18:16:05 +0200, "Frank Kabel"
wrote:

Hi
try
=SUMPRODUCT(--(MONTH(A1:A100=10),--(YEAR(A1:A100=2004),B1:B100)


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to extract month number from month name PM Excel Discussion (Misc queries) 2 January 19th 05 04:07 PM
Displaying value of specific cell within a range, with IF function...? Steve Excel Discussion (Misc queries) 1 January 14th 05 03:23 AM
what formula do i put for column m = column k minus column l in e. jenniss Excel Discussion (Misc queries) 5 January 6th 05 09:18 PM
How do I apply a Function to a column of cells and change there va Froggy New Users to Excel 1 December 23rd 04 01:09 AM
SUMIF(AND) FUNCTION Saariko Excel Worksheet Functions 9 October 28th 04 11:52 AM


All times are GMT +1. The time now is 02:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"