I have a range of data which looks like something like this:-

london -2
london -2
london -1
wales -2

I am trying to create a formula that counts the number of times the first
column contains -1 and the second column contains -2, so far I have come up
with the formula:-

=SUM(IF(AND($B$4:$B$11="london",$C$4:$C$11=-2),1,0))

But this only returns a value of 1, can you help?

Hi

This needs to be entered as an array formula so commit with
Ctrl+Shift+Enter.
Excel will include the curly braces { } when you do this. Do not type
them yourself.
{=SUM(IF(AND($B$4:$B$11="london",$C$4:$C$11=-2),1,0))}

for a non-array solution you could use
=SUMPRODUCT(--($B$4:$B$11="london"),--($C$4:$C$11=-2))


--
Regards

Roger Govier


"nir020" <(E-Mail Removed)> wrote in message
news:3A3F717E-FD4A-434B-AF05-(E-Mail Removed)...
>I have a range of data which looks like something like this:-
>
> london -2
> london -2
> london -1
> wales -2
>
> I am trying to create a formula that counts the number of times the
> first
> column contains -1 and the second column contains -2, so far I have
> come up
> with the formula:-
>
> =SUM(IF(AND($B$4:$B$11="london",$C$4:$C$11=-2),1,0))
>
> But this only returns a value of 1, can you help?
>
>
>
>