Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
=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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I format a value when using it within a cell reference tha. | Excel Worksheet Functions | |||
formula to return the value of a cell based on a looked up true reference | Excel Worksheet Functions | |||
Formula with text and reference to a date cell | Excel Discussion (Misc queries) | |||
Using a formula in a Hyperlink Cell reference | Excel Discussion (Misc queries) | |||
name of another worksheet in cell for reference | Excel Worksheet Functions |