Try...
=SUMPRODUCT(--ISNUMBER(MATCH(Branch,{"London","New
York"},0)),--(ANIU<"X"))
Hope this helps!
http://www.xl-central.com
In article ,
"Andrew Mackenzie" wrote:
Hi All,
Would appreciate any help with this one.
I have a table of date with a column range named Branch which contains
either London, New York, Paris or Milan.
I have another column range named ANIU which contains either a blank or an
"X" which refers to accounts which are no longer in use.
I want a formula to count the number of accounts in both London and New York
but I want to exclude accounts that are no longer in use in London or New
York.
So far I have got:
=COUNTIF(Branch,"=London")+COUNTIF(Branch,"=New York")-countif(ANIU,"=X")
But this obvously will substract accounts not in use from the Paris and
Milan branches. Can anyone help please,
Cheers,
Andrew