How to get exact match in semi-complicated countifs() formula

First, the formula I'm currently using:

=countifs('page1'!AF:AF,$L6,'page1'!AA:AA,"="&RIGHT(M$2,3))+countifs('page1'!AF:AF,$L6,'page1'!AB:AB,"="&RIGHT(M$2,3))+countifs('page1'!AF:AF,$L6,'page1'!AC:AC,"="&RIGHT(M$2,3))+countifs('page1'!AF:AF,$L6,'page1'!AD:AD,"="&RIGHT(M$2,3))+countifs('page1'!AF:AF,$L6,'page1'!AE:AE,"="&RIGHT(M$2,3))+countifs('page1'!AF:AF,$L6,'page1'!AF:AF,"="&RIGHT(M$2,3))

Alright, now, what I'开发者_C百科m attempting to match against is:

I-A
II-A
III-A
etc

The problem is that if within the ranges I'm looking there are patterns that loosely match the terms above, then the countifs() returns true without fully evaluating the entire string. In other words, I am specifically looking for strings "II-A" in said formula, but countifs() is returning counts for all strings that are not only "II-A" but "I-A" as well. Naughty countifs()!

Is there an easy way to do this. I seriously do not want to have to rethink/rewrite these formulas. I found a method called exact() but rolling that in just seems painful. Any help would be much appreciated.

EDIT EDIT EDIT

Here's a test table...

   A       B        C
1 I-A   II-A    1-Jan
2 II-A  III-A   2-Jan
3 I-A   II-A    2-Jan
4 I-A   II-A    3-Jan
5 II-A  III-A   4-Jan
6 I-A   II-A    7-Jan

=COUNTIFS($CC:$CC,"="&$C2,$AA:$AA,"="&$A1)+COUNTIFS($CC:$CC,"="&$C2,$BB:$BB,"="&$A1)

This mock test formula results in 0, but what I'm looking for is 1, because one I-A occurs on 2-Jan...

The same formula for 1-Jan should give me 1, and 1 for 3-Jan, and 7-Jan respectively. I'm trying to count the number of I-A, II-A, III-A for each date IE answering the question, "How many times does II-A occur on Jan-2, and then on Jan-3, etc?" Note that this is test data. The actual data actually has many more dates and many more kinds of values in place of the contents of the cells. For example purposes, however, this example data should be sufficient.

Thanks!

I'm not sure that it is COUNTIFS that is the problem. A very simple example using Excel 2008 gives:

Cells: A2 - A4 have I-A, II-A, III-A in them respectively. Same values in C2-C4.

In D2 I have:

=COUNTIFS($C$2:$C$4,"="&$A3)

and this yields 1 which is the correct answer for an exact match. Without pulling your formula apart it does have RIGHT(M$2,3) in there which will only match the last 3 characters, is that is what could be happening?

Hope this helps,

Dave