I need an excel formula

I need an updated formula to correctly count a total number for me. See the attached sheet.

On the INFO tab, years 2008 and 2011 count correctly. Years 2009 and 2010 are over counting for a total of 6. (4 in 2009 and 2 in 2010). These are highlighted in yellow.

I verified those numbers by filtering the data on the DATA tab and seeing the count. I have verified it as well by using other numbers on my production sheet as well.

What could be causing the count to count incorrectly? Formula or data? Please help!
4-1-11.xlsx
LVL 1
wrt1meaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

TimBusiness Systems AnalystCommented:
COUNT will count a " "
0
wrt1meaAuthor Commented:
I am using this as my formula:

=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"))
0
sijpieCommented:
The formula gives the correct result:
If in the data sheet you filter column I on 2009, and column M on 'Not 2009' then the count of items is 556, which is the same as the formula.
0
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

sijpieCommented:
April 1st perhaps? ;)
0
wrt1meaAuthor Commented:
No April fools joke! :) Here is what I get when I filter:

See attached filtered file for 552 records of 2655 total.
Filter-Results-552-of-2655-Total.xlsx
0
wrt1meaAuthor Commented:
The 552 out of 2655 was for 2009, here is the filter for 2010:

474 out of 2655
Filter-Results-474-of-2655-Total.xlsx
0
sijpieCommented:
Then why does my filter show 556 items for 2009? that is really weird.

Your filter for column M is wrong: your formula counts all where the entry in column M is not 2009 - ie empty cells and cells with a date outside 2009.

In your filtered example you have only filtered for empty cells in column M
0
wrt1meaAuthor Commented:
sijpie, OK so how to I get the formula to count like I have it filtered? Because I have confirmed the actual count (completely remeoved from the filter) to be 552 and 474, which totals 6. 6 is the exact number I am off by.
0
ragnarok89Commented:
I think the issue might be in your fmlas in col C. The formula is similar, but you have some extra in there.

Your sum product checks 6 conditions, and counts a row IF

the first two conditions are true
either the 3rd, or 4th or 5th is true
the last condition is true

So, I would guess your formula might need some tweaking OR some of the values in columns I or M or K are wrong.
0
barry houdiniCommented:
As sijpie says, you are filtering just by blanks in column M (and not by dates outside the target year) therefore this formula in C2 copied down should get the results you expect

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

regards, barry
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
Patrick MatthewsCommented:
bouscal,

>>COUNT will count a " "

No, it won't.  COUNT only counts numeric values.

Perhaps you were thinking of COUNTA, which will count all non-empty cells, even cells with formulae that evaluate to a zero length string.

Patrick
0
wrt1meaAuthor Commented:
Looks like this is gonna work!
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.