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

C

Chrism

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.
 
G

Guest

=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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUMPRODUCT and format problems (2) 2
Reference to another worksheet cell in a sumproduct formula 1
Formula using a date range 5
Sumproduct where cell values are the same in a sorted column 1
Can I use a named range in SUMPRODUCT? 2
Array?? 11
cell reference 4
Lookup text in array & return cell reference 5

Top