How can I use a cell reference in Sumproduct array formula?
 

I have a table with dates down the and employee numbers across the top.
I have SUMPRODUCT formulas in all the cells to gather data from named
arrays from a database in the spreadsheet. I'd like to avoid munually
changing (either individually or with REPLACE) date and employee number
references in each formula in each cell. my formula looks like:
{=SUMPRODUCT((Date=DATEVALUE("11/4/04"))*(Audempno=33758)*(Units))}
also, I used a previous suggestion from this forum on another similar
spreadsheet (successfully...for fiscal 3/05)with copying the whole
spreadsheet and changing the data and formulas to this one (fiscal
11/04) and now I get zeros as a result. Any suggestions there?

Thanks again-I hope I'm not going to the well too often.

=SUMPRODUCT(--(Date=B2),--(Audempno=C2),Units)

where B2 holds the date and C2 the employee number

also the function datevalue is obsolete, you might as well use

Date=--"11/4/04"

instead



Regards,

Peo Sjoblom

"Chrism" wrote:

I have a table with dates down the and employee numbers across the top.
I have SUMPRODUCT formulas in all the cells to gather data from named
arrays from a database in the spreadsheet. I'd like to avoid munually
changing (either individually or with REPLACE) date and employee number
references in each formula in each cell. my formula looks like:
{=SUMPRODUCT((Date=DATEVALUE("11/4/04"))*(Audempno=33758)*(Units))}
also, I used a previous suggestion from this forum on another similar
spreadsheet (successfully...for fiscal 3/05)with copying the whole
spreadsheet and changing the data and formulas to this one (fiscal
11/04) and now I get zeros as a result. Any suggestions there?

Thanks again-I hope I'm not going to the well too often.