Extracting data inexcell 2003

[Guest]

I have 2 spreadsheets, one listing a list of sales, the other showing a
summary of sales by each month. how do i get the summary from the detailed
list by month.

 

[Guest]

Assuming there is a date associated with each sale:

=SUMPRODUCT(--(MONTH(A2:A100)=6),B2:B100)

Column A contains Dates
Column B contains Sales

Above give sales for June (ignoring year)

=SUMPRODUCT(--(MONTH(A2:A100)=6),--(YEAR(A2:A100)=2007),B2:B100)

Above give sales for June 2007

HTH

 

[Guest]

Thank you very much I will give it a try. Thanks again.

 

[Guest]

Hi Toppers
Thanks a lot but is the syntax correct as I keep getting formula errors.
regards
john

 

[David Biddulph]

If you don't want to stretch the clairvoyant powers of the group, it might
be handy if you tell us what error message you're getting. Where in the
formula does the cursor sit when Excel reports the error?

What do your windows regional settings use for separators? Are you sure
that lists are separated by commas, or might they be semi-colons in your
system?

 

[Guest]

I have now sorted it out.
Thanks very much foe all your help.
John

 

[David Biddulph]

Glad it's sorted. Perhaps you could let us know why Toppers' formula wasn't
working for you and how you solved it, for the benefit of the group archive
and other readers who may have similar problems?

 

[Guest]

David
One other question. What If I wanted to rerieve data from another worksheet
what would be needed.
thanks.
john

 

[David Biddulph]

Change =SUMPRODUCT(--(MONTH(A2:A100)=6),B2:B100) to
=SUMPRODUCT(--(MONTH('Sheet 2'!A2:A100)=6),'Sheet 2'!B2:B100)
if you want to retrieve data from a sheet called Sheet 2.