Count or index

QuestionEdit

I'm needing help to count 2 conditions being met in 2 column.

What would the formula be to count all "NSW" that is "open"? And what about to count the number of total dependant for all "NSW" that is "open"?

AnswerEdit

The pictures on here are always hard for me to read (if you want to mail me directly I'm on aidan.heritage@virgin.net) so hopefully this answer will make sense

to do a multiple condition countif you can use sumproduct, using a neat trick that converts logical (true/false values) into the 1's and 0's

=sumproduct(--(firstrange="NSW"),--(SecondRange="Open"))

replace firstrange and secondrange with the actual ranges - you can extend this to as many conditions as you want just by repeating the

--(Range=test)

bit

NOTE also that it doesn't have to be an = in the range=test - it just needs to be a formula that would produce true or false