Sumproduct ignoring 0 values

I have this formula:

=SUMPRODUCT((InFill_Dates>=$A86)*(InFill_Dates<=$A86+6)*ISNUMBER((INDEX(Data_Table,,MATCH(D$1,Catagories,0)))))

Which is counting the number of cells with a number in that fall between the specified dates (A86+6) and are under the category in D1. This is looking in a sepeate worksheet for the answers. This works great for all the currently entered data.

I have set up the future cells to automatically source their data from elsewhere (this excel file runs very slowly, so by entering the data into a seperate file and then this file sourcing the data from that one it prevents the user from waiting for the cells to re-calculate every time data is entered). However, by setting up the formula it has generated a 0 in all the cells yet to be filled in the source file.

This means that the above formula fills in the cells utilising the 0's - so currently the future displays a 7 for each week (1 0 for each day). I have tried hiding the 0's but the formula still recognises them. I was wondering if there was a way to modify the above formula to only count numbers 1 and above?

Making a dummy workfile would be quite diffiicult so I will do my best to explain the problem, but I can do so if needed.

Thanks

This is a very basic file, I can't replicate the actual file more closely, mainly because I'm not entirely sure how to.

Sheet 1 is where you would put in a number into column B, C or D IF that particular category was present, so some days have 1 or more of the three categories present.

Sheet 2 is used to calculate the number of times that category was present within the week. I can't use the formula pasted above because the actual file is far more complex and utilises and excel list and I don't know how to recreate that. But it works as I stated above, by taking the name of the category at the top of the column (category 1,2 or 3) and looking in the first sheet under that category between the date in column A + 6 days.

The problem comes from row 17 downwards in sheet 1. In the actual file these cells are set to get their data from a seperate file and are all for future dates (thus you can input the data into the other file and then just update this one). Doing so fills all the cells with a '0' - which I have just done manually in the test file. This means that the formula auto fills in the future weeks in sheet 2 (and months in the actual file) as it counts the 0's as a number. Thus, the week commencing 28/01/2013 would displays a count of 4 in each category rather than remaining blank or at 0.

One workaround is just to ignore the fictious data entered in the future and just wait until I have a weeks real data, enter it and then just work on that basis. But it would be nice to set the graphs up for the future as well so I never have to enter this file (it runs incredibly slowly).

You will have to forgive me as we have well and truly gone over my head. The formula that I pasted into the first message works perfectly for what I want it to do (except it factors in 0) - other than changing it to ignore 0 it must work in precisely the same way.

From what I gather from looking at your two formula's is that they will work differently and not take into account the category of the column. There are about 10 columns in the second sheet and they are not all in the same order as in the first so this allows it to track the column across different sheets. The formula also accounts for the fact that there may be varying numbers of rows between two dates (in the example file, imagine if there were 1,2,3 or more rows for each date) - so the formula adapts to encompass all the rows required. Pasting your formula, sixthsense, returns a #Name? error - after changing the name of the data sheet.

Looking at your code sixthsense I saw this <>"" which looks something like what I was expecting. I was attempting something along the lines of the exact same code but inserted somewhere a condition of >0. But I couldn't work out where to place it