Search code examples
excelexcel-formulaexcel-2013libreoffice-calc

Excel : Count unique values using conditions and combos

To simplify this, let's say that I have a spreadsheet in Excel 2013 (or LibreOffice) where I have a list with rows where I can see the values below :

(Sorry, image upload is not currently working for me. I will substitute the monospaced text with a snapshot as soon as possible)

Country City        Person  Car Home
USA     New York    Alice   1   1
USA     New York    Bob     1   0
USA     Washington  Charlie 1   1
Canada  Ontario     Alice   0   1
Canada  Ontario     Bob     1   0
Canada  Ontario     Charlie 1   0
Canada  Ontario     Charlie 1   0
Canada  Toronto     Alice   1   1
Canada  Toronto     Bob     0   0

Combo selector : 

Country -> Canada
City    -> Ontario

Totals : 

        SUM     Distinct people     
Car     3       2       
Home    1       2

Values can be repeated. we can see two lines where Charlie has 1 car in Ontario (Canada).

Then I have two combos to filter by country and city (I will simplify considering only the city) and in the bottom I want to calculate using formulas the following :

  • Given a selected city in the combo, how many cars (and homes) I can add up. Once I defined ranges I ended up with the formula below. That is, There are 3 cars in Ontario, that is 3 :

    =IF(A15<>"All",SUMIF(RegionRange,A15,Column1Range),SUM(Column1Range))

  • The actual question. Given a selected city in the combo, how many different people have a car in that city. That is, although there are 3 cars in Ontario, there are only 2 people (Bob and Charlie). Having a look at a question about how to count unique values in Excel I could use the following formula, but that wouldn't take into account the filter in the combos :

    =SUM(IF(FREQUENCY(MATCH(Column1Range,Column1Range,0),MATCH(Column1Range,Column1Range,0))>0,1))

My question is what formula I can use instead of the previous one to take into account the filters in the combos.

Please note that I want the data to stay visible and update totals as I select something in the combos, so what I want is a formula to calculate it, and not the use of autofilters.

Solution

  • You can use this array formula:

    =SUM(IF(($B$2:$B$10=$H$1)*($D$2:$D$10>0),1/COUNTIFS($C$2:$C$10,$C$2:$C$10,$D$2:$D$10,">0",$B$2:$B$10,$H$1)))

    being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

    enter image description here