Count or index

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:22 PM GMT

Question

Spreadsheet

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"?


Answer

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

Advertisement

©2017 eLuminary LLC. All rights reserved.