# I need a formula corrected

See the attached file

The formulas I am using seem to return data that is incorrect (highlighted in yellow) for previous years. For example, there is little information available for the years 2000-2007 but its returning a large number of tracts and footage that are very similar.

I want to think that the formula I am using is pulling data correctly for the more recent years, but now I am unsure.
3-8-11.xlsx
Saurabh Singh Teotia

Use this formula in Column-F..

=SUMPRODUCT((((DATA!\$B\$2:\$B\$10000>\$D2)+(DATA!\$C\$2:\$C\$10000>=\$D2)))*(((DATA!\$B\$2:\$B\$10000<=\$E2)+(DATA!\$C\$2:\$C\$10000<=\$E2)))*(DATA!\$E\$2:\$E\$10000="N"))

Saurabh...
wrt1mea

Saurabh...

That didnt seem to work right. There are no acquired tracts for the year 2000 and its returning 268.
The blanks in some of the date fields are affecting results, try this version in G2 copied down to avoid counting blank dates

=SUMPRODUCT(((DATA!\$B\$2:\$B\$10000>\$D2)+(DATA!\$C\$2:\$C\$10000>=\$D2)>0)*((DATA!\$B\$2:\$B\$10000<=\$E2)*(DATA!\$B\$2:\$B\$10000<>"")+(DATA!\$C\$2:\$C\$10000<=\$E2)*(DATA!\$C\$2:\$C\$10000<>"")>0)*(DATA!\$E\$2:\$E\$10000="N"),DATA!\$D\$2:\$D\$10000)

regards, barry