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

4-1-11.xlsx
LVL 1
###### Who is Participating?

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.

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

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

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