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

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