asked on # 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

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

Microsoft Excel

Saurabh...

That didnt seem to work right. There are no acquired tracts for the year 2000 and its returning 268.

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

=SUMPRODUCT(((DATA!$B$2:$B

regards, barry

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

Thanks again!

Use this formula in Column-F..

=SUMPRODUCT((((DATA!$B$2:$

Saurabh...