And/Countif formula

[Guest]

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?

 

[Roger Govier]

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))