Calc: counting rows according to different criteria in different columns

[alfredino]

Hi,
I am using the latest libreoffice 26.8. I’m having difficulty counting the number of rows that are unique based on the first column and that satisfy a specific condition in another column (I know how to do this in a programming language, but I don’t know how to do it in calc).

Column A Column B Column C
Country1 Yes No
Country1 Yes No
Country2 No Yes
Country3 No Yes
Country1 Yes No

I tried with the unique function, but it only extracts one column. Otherwise I could combine it with countif. It is something close to this, but there is not distinct function in calc.
Thank you

[Villeroy]

Generating aggregated tables from data tables is a typical database task. Advanced spreadsheet applications have pivot tables for this.
https://help.libreoffice.org/26.8/en-US/text/scalc/guide/datapilot_createtable.html?DbPAR=CALC#bm_id3148491

boolean_pivot.ods (20.0 KB)

[alfredino]

Thank you. Actually, I don’t want to create an aggregate table, but counting only one time rows by first column and that satisfy a given condition in a different column.

[yclik]

Hello
another suggestion
boolean1.ods (20.2 KB)

[alfredino]

Thank you, but it does not work as expected. I would like to count how many countries satisfy a condition. In the example above, Country1->1 and others 0 for column B and Country2->1 and Country3->1 for column C (so totally 1 and 2).

[alfredino]

After searching for a couple of hours, I solved with the following (thank to this):

=COUNTA(UNIQUE(FILTER(firstColumn,conditionColumn=condition)))

=SUM(–(LEN(UNIQUE(FILTER(firstColumn,conditionColum=condition,"")))>0))