First I am somewhat new to excel, so I hope I use the right terminology. I created a spread sheet to track received files from different accounts. The sheet is setup so the accounts run across the top columns and the rows are broken up by week numbers with the week ending date also displayed. I used sumproduct to filter the data by week number based on the weekending date in column B and display how many times an account is listed using this function.
=SUMPRODUCT(--(Data!Q2:Q2000>=B5-6),--(Data!Q2:Q2000<=B5),--(Data!A2:A2000=155))
From the Data worksheet, column Q is the date files are received and column A is the account number.
Now here is my problem and I am not sure if it is even possible. When I take and paste the function from one row to the next so I can pull the data for each week number, the rows and columns adjust based on the movement in the table. Is there a way to lock part of the function because I always want it to filter through Q2:Q2000 and A2:A2000 even though the weekend ending reference in column B will be changing? It would be nice if sumproduct could use Q:Q and A:A, but from what I have read I understand the array cannot work that way. Can the function be reconfigured or can I change the way I copy/paste the function to avoid having to manually edit thousands of functions so the display correctly?
Thanks
Bookmarks