SUMPRODUCT breakdown by date

A

Aaron Saulisberry

I'm currently using the formula below to breake it down by month but I also
need to have it breakdown those months by year.

SUMPRODUCT(--(MONTH(Sheet!A1:A10)=1),--(Sheet!B1:B10=Sheet1!C1:C10),Sheet1B1:B10)

This formula compares two columns and if those columns have the same data
for the same month then it returns the sum of the data. The only problem is
is does not distingush between year, which I need it to do so.

Any help is appreciated.

1-JAN-05
 
A

Arvi Laanemets

Hi


SUMPRODUCT(--(TEXT(Sheet!A1:A10,"yyyy.mm")="2006.01"),--(Sheet!B1:B10=Sheet1!C1:C10),Sheet1B1:B10)
 
A

Aaron Saulisberry

This keeps returning zero... does the date in column A have to be in the
format "yyyy.mm"?
 
B

Bob Phillips

SUMPRODUCT(--(YEAR(Sheet!A1:A10)=2005),(--(MONTH(Sheet!A1:A10)=1),--(Sheet!B
1:B10=Sheet1!C1:C10),Sheet1B1:B10)

is an alternative

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
A

Arvi Laanemets

Hi


Aaron Saulisberry said:
This keeps returning zero... does the date in column A have to be in the
format "yyyy.mm"?
Click to expand...

No. It simply must be in any valid date format. I just checked to be sure,
and it even worked when instead dates in column A were date strings (column
A formatted as text).
 
A

Arvi Laanemets

PS. To check, is a date in valid format, change the cell format to General -
the date must be displayed as number now (p.e. todays date will be displayed
as 38741)
 
A

Aaron Saulisberry

I need to be a little more patient when hitting the reply button... not even
two minutes after I posted my reply I figured it out.

Thanks for the help everybody... greatly appriciated.
 
Top