Sumproduct Problem

[J. Trucking]

Hello,

I have exported a table from Access for repairs on various types of
equipment. I want to calculate the reapirs for each unit (each has a
specific unit number) for each year of its existance.There are three
columns: ReapirDate, UnitNumber, Cost. I have named these three
ranges. I have created a second worksheet and have to boxes that the
user can fill in to narrow the search for a particular year (A2) and
unit number (A3). What I want to do is sum the repairs for that unit
and year. Here's what I did:

=SUMPRODUCT((YEAR(RepairDate)=A2)*(UnitNumber=A3)*Cost)

But this always returns a value of zero even when I know it's not
zero. My UnitNumber field was a "text" field in Access because some
of the units have a letter behind them (ie) 75A. However, I was
hoping to create something as there are over 15000 records. I have
absolutely no clue how to build a pivot table so I was hoping to
create something with a worksheet function. Any help/suggestions
would be greatly appreciated.

Thanks in Advance,

Joe

 

[Bob Phillips]

First guess is to look at the dates, are they real dates? IF you put =a2+1
in a cell (assuming the dates are in A), what do you get?

--
HTH

Bob

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

 

[J. Trucking]

Thanks for the response Bob. When I get this, I get the next day. I
have tried the sumproduct with various combinations using just the
date cell (ie) pulling out specific years and months. I think I have
narrowed it down to the unit number. Any ideas?

 

[Bob Phillips]

Maybe leading/trailing spaces? Try

=SUMPRODUCT((YEAR(RepairDate)=A2)*(TRIM(UnitNumber)=A3)*Cost)


--
---
HTH

Bob


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