I need an excel formula

I need an excel formula to correctly count the number of acquired tracts (See attachment). The problem I am having is that it is over counting the total by 78. I have confirmed the numbers through counts and physical counts.

I think the problem is that I am trying to make sure I count acquired tracts and acquired permits seperately and there is a conflict causeing me to count an additional 78. 189 has been confirmed as the correct acquired permit count, so the additional 78 I think is in the acquired tracts. Its got to be double counting somewhere. I have been trying to figure this out, but its time for me to look a way for a minute.

In the attachment, i have tried to include as much information as possible on the INFO tab. Data is on the DATA tab.
3-31-11.xlsx
LVL 1
wrt1meaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ragnarok89Commented:
Formulas in C10, C11, C12, and C13 are correct, their counts are correct. So as far as I can tell, 1223 Acquired Tracts is correct.

Unless some of the values in column I and/or K are wrong....
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saqib Husain, SyedEngineerCommented:
Can you identify some of the rows which have been wrongly counted?
0
wrt1meaAuthor Commented:
Man, thats the thing. I know 1,470 is correct. You can filter the "N" on column K and get that total real quick. I know we have acquired 1,334 tracts and permits. Of those 1,334, 189 are permits. 189 has been confirmed as well. Believe me, I am literally pulling my hair out over this.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Saqib Husain, SyedEngineerCommented:
Then as ragnarok89 has said there are mistakes in the data entry. You will have to go one by one and check each entry. If you have the actual count per year and per month then that would make your life a bit easier.

Saqib
0
McOzCommented:
Agreed with ragnarok89, the functions are doing their job right. I did notice that there are a few values in column C that instead of AREA1 are abc-AREA1. Maybe this is on purpose (and there are not 78 of them), but it made me think you may want to make sure there is no bogus data in the sheet.

Bottom line, your functions are correct, the data itself is the problem here...

Good luck!
0
wrt1meaAuthor Commented:
Man, I was hoping that I was missing something in a formula instead of having bad data.

Looks like I will have my work cut ouf for me for quite a bit of time!

How should I handle the question and points?
0
Saqib Husain, SyedEngineerCommented:
I think the first comment is a comprehensive comment. Just accept it.
0
wrt1meaAuthor Commented:
Thanks for the quick response.
0
kinseyCommented:
Check your figures,
If you sort the DATA sheet by Column K then by Column I you will intd that the Acquired Tracts counts are:
2008 6
2009 589
2010 518
2011 110
as the INFO sheet shows.
I used
COUNTIFS(DATA!I:I,">="&A15,DATA!I:I,"<="&B15,DATA!K:K,"N")
and got the same numbers
0
byundtMechanical EngineerCommented:
wrt1mea,
I realize that the question has already been answered, but I believe your issue occurs due to double-counting rows as both Acquired Tracts and Acquired Permits. There are exactly 78 such (33 in 2009 and 45 in 2010), as shown by a SUMPRODUCT formula like:
=SUMPRODUCT((DATA!$I$2:$I$10000>=$A11)*(DATA!$I$2:$I$10000<=$B11)*(DATA!$M$2:$M$10000>=$A11)*(DATA!$M$2:$M$10000<=$B11)*(DATA!$K$2:$K$10000="N"))


It is also possible to produce your counts using a PivotTable.

The sample workbook shows both the SUMPRODUCT and PivotTable.

Brad
3-31-11Q26925737.xlsx
0
byundtMechanical EngineerCommented:
Rows being double-counted include 21, 76, 170, 232, and 277.

I copied down the following formula to find them:
=AND(YEAR(I2)=YEAR(M2),I2<>"",M2<>"")                     'AutoFilter for a value of TRUE at the same time as AutoFilter column K for "N"
0
byundtMechanical EngineerCommented:
You will get the correct count (1145 total) for Acquired Tracts by excluding Acquired Permits by copying down this SUMPRODUCT in cell C2:
=SUMPRODUCT((DATA!$I$2:$I$10000>=$A2)*(DATA!$I$2:$I$10000<=$B2)*(((DATA!$M$2:$M$10000<$A2)+(DATA!$M$2:$M$10000>$B2)+(DATA!$M$2:$M$10000=""))>0)*(DATA!$K$2:$K$10000="N"))

Brad
0
wrt1meaAuthor Commented:
Using the following formula from byundt saved myself and 6 others a substantial amount of time (4-6 hours each) and resources that would have been otherwise spent correcting approximately 250 records unnecessarily.

=SUMPRODUCT((DATA!$I$2:$I$10000>=$A2)*(DATA!$I$2:$I$10000<=$B2)*(((DATA!$M$2:$M$10000<$A2)+(DATA!$M$2:$M$10000>$B2)+(DATA!$M$2:$M$10000=""))>0)*(DATA!$K$2:$K$10000="N"))

Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

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.