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!
Who is Participating?
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


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

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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.
April 1st perhaps? ;)
wrt1meaAuthor Commented:
No April fools joke! :) Here is what I get when I filter:

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

474 out of 2655
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
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.
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.
Patrick MatthewsCommented:

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

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