SumProduct Not Working 1

[shelby55]

Hi

I am using Excel 2002 that is accessing data from an Access 2002 database.

I have data by month and department. I have successfully (with lots of help from Skip) been able to use SumProduct and named ranges to reference the most recent period but also the combined total of the periods:

=SUMPRODUCT((MyDepartments=DeptSelected)*(MyDates<=DateSelected)*(MyDates>=Min_Date)*(Budget))

Now I've added another field in my query and edited the MS-Query query to include the fields for ALC_days and Total_days. The named ranges and values are working fine within my graph but I get #N/A when I try to use the following for ALC:

=SUMPRODUCT((MyDepartments=DeptSelected)*(MyDates=DateSelected)*(Total_ALC))

The only thing I can think that is different from the previous finance data that I used is that not all departments have alc_days and total_days. However, #N/A shows up when I'm selecting departments that have them and the data is clearly showing in the columns. I've checked and re-checked my named ranges and they are correct.

Any advice greatly appreciated.

 

[SkipVought]

Hi,

Does Total_ALC have the same row range as MyDates & MyDepartments. I suspect not.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Sorry but I forgot to post back....yes Total_ALC has the same row range but I found from reading that if the column is blank then it wouldn't work. I inserted a column with "if isblank..." etc. to change null to zero and now it works.

See, you taught me to fish!!!

 

[SkipVought]

Thats GREAT!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}