[Sean]

How could I express this in a formula- I wish to add up the number of
instances that stock doesn't exist and no sales have been made, hence
a possible reason for no sales.

e.g.

Z8 = Sales in London which are 0
N46 = Stock for the London depot which is 0
Z12 = Sales in Manchester which are 20
N50 = Stock for the Manchester depot which is 0
Z16 = Sales in Bristol which are 0
N54 = Stock for the Bristol depot which is 0

The answer to above should be 2

I have a range of 30 locations, divided into 5 regions, but they are
not in a contiguous range

[PCLIVE]

This may work for you.

=SUMPRODUCT(--(RIGHT(Z8:Z16,2)=" 0"),--(RIGHT(N46:N54,2)=" 0"))

The two ranges used MUST be the same exact size. This also assumes that
your data in both locations is in the same order. (ex. - it starts with
London then Manchester followed by Bristol, and so-on for the rest of your
regions.

HTH,
Paul

--

"Sean" wrote in message
...
How could I express this in a formula- I wish to add up the number of
instances that stock doesn't exist and no sales have been made, hence
a possible reason for no sales.

e.g.

Z8 = Sales in London which are 0
N46 = Stock for the London depot which is 0
Z12 = Sales in Manchester which are 20
N50 = Stock for the Manchester depot which is 0
Z16 = Sales in Bristol which are 0
N54 = Stock for the Bristol depot which is 0

The answer to above should be 2

I have a range of 30 locations, divided into 5 regions, but they are
not in a contiguous range

[Sean]

Thanks Paul, it won't work as my ranges are not Contiguous, in that
Z9; Z15 relates to a different region which I don't wish to COUNT
within the first formula that I've shown as an example

[Sean]

This is how I advanced, I placed the Region name in a column and used
the following formula

=SUMPRODUCT((Z$8:Z$33=0)*(W$8:W$33="Midlands")*(N$ 46:W$71=0))

[Sean]

One small twist on this, how could I list the names of the locations
(which are in Z8:Z33) that have Zero sales and Zero Stock, in a string
format. eg. "London, Manchester, Bristol" etc