COUNTIF non duplicate values that begin with TWO choices

No spreadsheet attached, so untested:

Formula:

Please Login or Register  to view this content.

This is not as elegant as I had hoped, but it works...

Dont forget it's an array formula and must be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

You're welcome...

=sum(if(frequency(if(left('c300-c410'!c16:c1071,4)={"c300","c410"},match('c300-c410'!c16:c1071,'c300-c410'!c16:c1071,0)),row('c300-c410'!c16:c1071)-row('c300-c410'!c15)+1),1))
try this array formula
(shift+ctrl+enter)

Nflsales, an elegant solution!! I hadn't thought of that. Do you know why an OR function failed to work for me, when I tried it?

I think OR / AND Functions will not deal with array, it will give single true or false
for more details evalueate the formula, there you can find it.

Here is more refined solution.

Please Login or Register  to view this content.

I have used matrix multiplication to convert the n x 2 array in to n x 1.
So here is the explanation with a small example
--(LEFT('C300-C410'!C16:C1071,4)={"c300","c410"} portion will give a n X 2 array just as following:-

T T
F F
T F
F T multiplied by {1 , 1}
T F
T T
. .
. .
. .
n n

to give ( just as simple matrix multiplication does.. n x 2 multiplied by 2 x 1 to give n x 1

T * 1 + T * 1 = 2
F * 1 + F * 1 = 0
T * 1 + F * 1 = 1
like this till n..
this is how it will refine it and convert it into same sized array as MATCH('C300-C410'!C16:C1071,'C300-C410'!C16:C1071,0)) is of.
So what I actually meant is, this way , we can add more exceptions or inclusions to {"c300","c410"} array
by adding ",1" (without quotes ) in the mmult second argument {1,1} like for {"c300","c410", "c411"}, Mmult argument would be {1,1,1}.
You can find some serious and FLEXIBLE implementations of matrics multiplications in the sheet 2 of attachment in Post #9 in the following link:-
http://www.excelforum.com/excel-form...t-formula.html

File can be directly downloaded from here:-
http://www.excelforum.com/attachment...ller-mod1.xlsm

Vikas,

I have yet to find a "layman's guide" to MMULT. Can you point me towards some good tutorials?

Hi, Glenn,
I have made edited the above post for some advanced implementation.
Actually, I learnt some basics from XOR, an expert here, and rest is my own application.
if you know simple matrix multiplication, you can easily think of applying it.
However, you can refer excel inbuilt help and google.
If you learn anything new then please share it.
I am gonna make a full article on it and share it soon in tips and tutorial section with OR and AND conditions factored in it.
Meanwhile, you can think of it as well.
Good Luck.

Originally Posted by Glenn Kennedy

I have yet to find a "layman's guide" to MMULT. Can you point me towards some good tutorials?

I tried to explain how it works in this post from several years ago...

http://www.tech-archive.net/Archive/.../msg00758.html

Very nice post Volko sir,
Exactly what I meant, may be I reinvented it.

Hi tony sir,
If you can tell with your nice speed checking codes,that:-
Which one is faster Array multiplication or
Matrics multiplication..?
Because I have some nice ideas with Matrics multiplication...

The code to test calculation times can be found here:

http://msdn.microsoft.com/en-us/library/aa730921.aspx

If you want me to test if for you, you'd have to give me a sample file with data and the formulas you want to test.

Okay thanks,
I will prepare the matter, try the code myself first and let you know if there is any problem.

Since I do this kind of testing often I put the code in my Personal.xls file then added buttons to my toolbars that run the code.