I need to create a COUNTIF formula that has two unique criteria that must be met. The criteria resides in two unique cell ranges. The resulting total should be if both criteria are met, not a total that counts each criteria separately and then adds them together. I tried the following and realized that the total for each criteria was being counted and then added together to give me the final number.
=COUNTIF(X:X, "99999") + COUNTIF(Y:Y, "TEXT")
I'd appreciate any suggestions.
Thanks, MLCall
Last edited by MLCall; 07-19-2012 at 10:21 AM. Reason: To show solved.
Please Login or Register to view this content.
I believe with 2003, you can't use the entire column. Use a maximum expected range (or a dynamic named range) instead.
For example
=sumproduct(($x$1:$x$5000="99999")*($y$1:$y$5000="text"))
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
I appreciate your quick support. This doesn't seem to be working for me. I'm getting a count of 0 for every combination I've tried to far. Here is a copy of what I typed: =SUMPRODUCT((L2:L236="60102")*(M2:M236="Algonquin"))
I'll continue to try and figure out what I'm doing wrong.
I tried this version first (included the dollar signs) and still got a result of 0. I'm trying to determine what I'm doing wrong.
Here is what I typed: =SUMPRODUCT(($L$2:$L$236="60010")*($M$2:$M$236="Barrington"))
Unless your numbers are formatted as text, remove the quotes from around the 60010
=COUNTIFS($L$2:$L$236,60010,$M$2:$M$236,"Barrington")
This will work
To Sturmmann, MLCall has Excel 2003 and so can't use COUNTIFS
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks