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

Commented:
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

COUNT will count a " "
0

Author 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

Commented:
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

Commented:
April 1st perhaps? ;)
0

Author 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

Author 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

Commented:
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

Author 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

Commented:
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

Commented:
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

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