Counting valid (or missing) cell data, based on other column

Trying to count how many “valid” (i.e., non-missing) cases I have, but based on the criteria from another column. For example, with this data…

1 Vietnam Asia 45
2 Yemen Asia
3 Belize Central America 70
4 Costa Rica Central America 9
5 El Salvador Central America

I’d like to be able to tell that 1 country in Asia (i.e., Vietnam) has data for column C, while 2 countries in Central America (i.e., Belize and Costa Rica) do. I presume I’d need two separate functions (one for Asia, one for Central America).

Typically I use COUNTIFS when I’m trying to count how often something appears (e.g., =COUNTIFS(B2:B5, “Asia”) would be 2). Or, I use COUNTBLANK if I’m interested in how much data is missing (e.g., =COUNTBLANK (C1:C5) would also be 2).

But, I’d like to know how many things in column C meet criteria from column B. I’ve used functions in LibreOffice for years, but apparently have never had to do something like this before, and I’m a bit stumped. Any help would be greatly appreciated!

You can use SUMPRODUCT for this kind of questions, as in the following tutorial (see advanved use of)

But a pivot-table can create the anwers for your whole table…

See Example with →Data→Pivottable:

pivot_count_Data_per_Continent.ods (11.3 KB)

1 Like

I use English-UK language and curiously, in Properties, the Data Fields panel is empty. It should show Count - Data2. If I OK from Properties the column goes blank. Looks related to bug Bug 118117 - Pivot table with column named “Data” as it is OK in

Changed column name and added Count - SomeData in:
pivot_count_Data_per_ContinentRenameColumn.ods (12.3 KB)

Posted bug report
Bug 154197 - Pivot Table with column named “Data” does not show in Data Fields pane -EDITING

The “Data” field is “reserved” for pivot tables.

It remains invisible as long as the DataPilot table contains at most one data field.

The most reliable workaround is to rename the field in the data source.

it seems it is locale-dependent: with me (locale de.DE german) i can use »data« without issues because the »reserved« field is named »Daten« -


Yes it is.
At the same time, the getName method of the specified field returns “Data” for any locale, where the localized name comes from - I don’t know.