wrt1mea

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

ASKER

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

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Thanks again!

Use this formula in Column-F..

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

Saurabh...