# Revision history [back]

### Counting rows where specific combination of values from two columns occur

Hi!

I will try to explain what I'm looking for with an example.

Given the table below

 |  A       B       C
-|---------------------
1| AA-     XYZ      a
3| AA-     AB-      c
4| XYZ     ZYX      d
5| ZYX     DB_      e
6| AC-     DA_      f
7| DA_     AB-      g


I need to count all the rows where the first (A) and third (B) column both contain a value that belongs to the set {AA-, AB-, AC-, AD-, DA_, DB_, DC_, DD_}. For the example above only row 3, 6 and 7 will be taken into consideration because both of the "important" columns contain values that are from that set.

Using COUNTIFS I tried to come up with a regular expression. I've used the ? since the values are limited and there is not chance of having something like AX- or DG_. Even the values that are not allowed are a part of a set on their own but since it's a larger set I have decided to use inclusion instead of exclusion:

=COUNTIFS(A1:A7, "[A?-|D?_]", B1:B7, "[A?-|D?_]")


Since I'm new to LibreOffice and Calc in particular I was hoping to get an explanation why this expression always returns and how to fix it.

I need this in order to verify some results from a software I'm working on which uses a similar table to extract the respective rows and check some of their values. Manual verification is really tedious. Just one table only contains ~90K rows and the set of allowed values is much larger which leads to a huge pool of possible combinations. That is why I decided to check some of the stats my software prints out and compare those with the results from LibreCalc.

### Counting rows where specific combination of values from two columns occur

Hi!

I will try to explain what I'm looking for with an example.

Given the table below

 |  A       B       C
-|---------------------
1| AA-     XYZ      a
3| AA-     AB-      c
4| XYZ     ZYX      d
5| ZYX     DB_      e
6| AC-     DA_      f
7| DA_     AB-      g


(see CSV at the end)

I need to count all the rows where the first (A) and third (B) column both contain a value that belongs to the set {AA-, AB-, AC-, AD-, DA_, DB_, DC_, DD_}. For the example above only row 3, 6 and 7 will be taken into consideration because both of the "important" columns contain values that are from that set.

Using COUNTIFS I tried to come up with a regular expression. I've used the ? since the values are limited and there is not chance of having something like AX- or DG_. Even the values that are not allowed are a part of a set on their own but since it's a larger set I have decided to use inclusion instead of exclusion:

=COUNTIFS(A1:A7, "[A?-|D?_]", B1:B7, "[A?-|D?_]")


Since I'm new to LibreOffice and Calc in particular I was hoping to get an explanation why this expression always returns and how to fix it.

I need this in order to verify some results from a software I'm working on which uses a similar table to extract the respective rows and check some of their values. Manual verification is really tedious. Just one table only contains ~90K rows and the set of allowed values is much larger which leads to a huge pool of possible combinations. That is why I decided to check some of the stats my software prints out and compare those with the results from LibreCalc.

AA- XYZ a
AA- AB- c
XYZ ZYX d
ZYX DB_ e
AC- DA_ f
DA_ AB- g


### Counting rows where specific combination of values from two columns occur

Hi!

I will try to explain what I'm looking for with an example.

Given the table below

 |  A       B       C
-|---------------------
1| AA-     XYZ      a
3| AA-     AB-      c
4| XYZ     ZYX      d
5| ZYX     DB_      e
6| AC-     DA_      f
7| DA_     AB-      g


(see CSV at the end)

I need to count all the rows where the first (A) and third (B) column both contain a value that belongs to the set {AA-, AB-, AC-, AD-, DA_, DB_, DC_, DD_}. For the example above only row 3, 6 and 7 will be taken into consideration because both of the "important" columns contain values that are from that set.

Using COUNTIFS I tried to come up with a regular expression. I've used the ? since the values are limited and there is not chance of having something like AX- or DG_. Even the values that are not allowed are a part of a set on their own but since it's a larger set I have decided to use inclusion instead of exclusion:

=COUNTIFS(A1:A7, "[A?-|D?_]", B1:B7, "[A?-|D?_]")


Since I'm new to LibreOffice and Calc in particular I was hoping to get an explanation why this expression always returns and how to fix it.

I need this in order to verify some results from a software I'm working on which uses a similar table to extract the respective rows and check some of their values. Manual verification is really tedious. Just one table only contains ~90K rows and the set of allowed values is much larger which leads to a huge pool of possible combinations. That is why I decided to check some of the stats my software prints out and compare those with the results from LibreCalc.

The example table from above as CSV (delimiter is space):

AA- XYZ a
AA- AB- c
XYZ ZYX d
ZYX DB_ e
AC- DA_ f
DA_ AB- g


### Counting rows where specific combination of values from two columns occur

Hi!

I will try to explain what I'm looking for with an example.

Given the table below

 |  A       B       C
-|---------------------
1| AA-     XYZ      a
3| AA-     AB-      c
4| XYZ     ZYX      d
5| ZYX     DB_      e
6| AC-     DA_      f
7| DA_     AB-      g


(see CSV at the end)

I need to count all the rows where the first (A) and third second (B) column both contain a value that belongs to the set {AA-, AB-, AC-, AD-, DA_, DB_, DC_, DD_}. For the example above only row 3, 6 and 7 will be taken into consideration because both of the "important" columns contain values that are from that set.

Using COUNTIFS I tried to come up with a regular expression. I've used the ? since the values are limited and there is not chance of having something like AX- or DG_. Even the values that are not allowed are a part of a set on their own but since it's a larger set I have decided to use inclusion instead of exclusion:

=COUNTIFS(A1:A7, "[A?-|D?_]", B1:B7, "[A?-|D?_]")


Since I'm new to LibreOffice and Calc in particular I was hoping to get an explanation why this expression always returns and how to fix it.

I need this in order to verify some results from a software I'm working on which uses a similar table to extract the respective rows and check some of their values. Manual verification is really tedious. Just one table only contains ~90K rows and the set of allowed values is much larger which leads to a huge pool of possible combinations. That is why I decided to check some of the stats my software prints out and compare those with the results from LibreCalc.

The example table from above as CSV (delimiter is space):

AA- XYZ a