Autofilling with Complex Forumulas

I'm working with Excel 2003, and this project will have an analysis sheet that uses pure formulas to pull from a data sheet...

I'm using the current code for my desired result:

=SUM(IF(Data!F1:F8000=A2,IF(Data!BN1:BN8000="N",IF(Data!N1:N8000="N",1,0))))

This uses multiple IFS to narrow down the results, and then counts what fits the logic from the data sheet. The "A2" is the name of the person I want as the first if condition.

Now here's the problem... I try to autofill to apply this formula to lower cells, but it updates EVERYTHING by 1, as in:

=SUM(IF(Data!F2:F8001=A3,IF(Data!BN2:BN8001="N",IF(Data!N2:N8001="N",1,0))))

All I want it to update is the "Ax" field, but leave the rest the same, as 2-8000 is the range of the data sheet that it needs to search through. I tried manually doing the formula 5 times, then selecting the 5 and pulling down autofill, but now it just jumps the numbers up in 5's!

Basically, any solution to only make autofill change the "Ax" field and leave the rest of the formula the same would save me a huge headache

---OR---

if there's some way to make it search the column without designating the range, I've tried:

=SUM(IF(Data!F:F=A2,IF(Data!BN:BN="N",IF(Data!N:N="N",1,0))))

But I end up with a !NAME error as a result. How do I code an entire column as the search field?

Thanks for the help, I'm a newbie and really don't know how to search for the answer to my question.

For your first formula, this should work for you:

=SUM(IF(Data!$F$1:$F$8000=A2,IF(Data!$BN$1:$BN$8000="N",IF(Data!$N$1:$N$8000="N",1,0))))

However, you can also use the following:

=SUMPRODUCT((Data!$F$1:$F$8000=A2)*(Data!$BN$1:$BN$8000="N")*(Data!$N$1:$N$8000="N"))

HTH

Jason

Thanks a ton, Jason...

I'm assuming the $ symbols locks the data from changing at it is autofilled to new slots?

No problem. And yes, that is correct.