=SUMPRODUCT but need to break out Jan 07 and Jan 08 results

[Neall]

I am currently using

=SUMPRODUCT(--(ISNUMBER('ALL PMRs 2007'!B$3:B$65536)),--(MONTH('ALL PMRs
2007'!B$3:B$65536)=1))

However now I need to make sure 2007 data for Jan,Feb, March etc is
seperated from the new 2008 data.

How can this be done?

 

[Carim]

Hi,

You can add a condition on the year ...
where is it located ?

 

[merjet]

Try similar to this:
=SUMPRODUCT(--(MONTH(Sheet1!$A$3:$A$31)=MONTH(Sheet1!A3)), --
(YEAR(Sheet1!$A$3:$A$31)=YEAR(Sheet1!A3)))

Change sheet name and ranges to suit.

Hth,
Merjet

 

[Matthew Pfluger]

MrExcel's podcast from yesterday dealt with a similar issue to this. It
proved very helpful to me.

http://mrexcel.libsyn.com/media/mrexcel/podcast662a.mp4

HTH,
Pflugs

 

[Bob Phillips]

Wouldn't your 2008 numbers be in the 2006 worksheet, thereby ... no problem?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Neall]

Thanks, maybe I am missing something but it doesnt seem to be working

here is what I am using

=SUMPRODUCT(--(MONTH('ALL PMRs 2007'!B$3:B$65536)=1*('ALL PMRs 2007'!B$3)),--
(YEAR('ALL PMRs 2007'!B$3:B$65536)=2007*('ALL PMRs 2007'!B$3)))

 

[merjet]

=SUMPRODUCT(--(MONTH('ALL PMRs 2007'!B$3:B$65536)=1*('ALL PMRs 2007'!B$3)),--

(YEAR('ALL PMRs 2007'!B$3:B$65536)=2007*('ALL PMRs 2007'!B$3)))

I expect it will work if you delete *('ALL PMRs 2007'!B$3) in two
places.
But I expected you wanted variables instead of 1 and 2007 so you
could
copy the formula to other rows. The above won't work with a date in
B3. You need to extract the month and year from the date.

Hth,
Merjet

 

[Neall]

Sorry I might be confusing you here, I have one Column with all dates
starting from Jan 07 (format is 13/02/2007 16:38) so I think option 2 that
you had described is what I need

I want to seperate and display the previous years data against this years.