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

=SumProduct((branch={"London"."New york"})*(aniu<"x"))

or

=SumProduct(((branch="London")+(branch="New York"))*(aniu<"x"))

JB
http://boisgontierjacques.free.fr/

On 1 avr, 17:25, "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

Try this:

=SUMPRODUCT((Branch="London")+(Branch="New York"),--(ANIU<"X"))

HTH
Elkar


"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

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

Hi,
=COUNTIFS(Branch,"London",A,"<x")+COUNTIFS(Branch ,"New York",A,"<x")

will do the trick

"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

Opps I made a mistake

=COUNTIFS(Branch,"London",ANIU,"<x")+COUNTIFS(Bra nch,"New York",ANIU,"<x")

"Eduardo" wrote:

Hi,
=COUNTIFS(Branch,"London",A,"<x")+COUNTIFS(Branch ,"New York",A,"<x")

will do the trick

"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