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
LVL 1
wrt1meaAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
...F2 of course, will be the same without the sum range, i.e. in F2 copied down

=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"))

barry
0
 
Saurabh Singh TeotiaCommented:

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...
0
 
wrt1meaAuthor Commented:
Saurabh...

That didnt seem to work right. There are no acquired tracts for the year 2000 and its returning 268.
0
 
barry houdiniCommented:
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
0
 
wrt1meaAuthor Commented:
Thanks again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.