Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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
2| AD-     ZYX      b
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
2| AD-     ZYX      b
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
AD- ZYX b
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
2| AD-     ZYX      b
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
AD- ZYX b
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
2| AD-     ZYX      b
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
AD- ZYX b
AA- AB- c
XYZ ZYX d
ZYX DB_ e
AC- DA_ f
DA_ AB- g