mardi 4 décembre 2018

Excel - Count The Occurrence of One Pair of Values Across Multiple Columns

I have a matrix:

A   B   C   D   E   F
67  1   43  48  49  49
12          35  50  50
            33      
22  33              33

I want to count the number of rows that have entries for at least one pair of pre-specified columns. It is simply a matter of whether the cell has a value or not (rather than a specific value). The column pairings are A-D, B-E, and C-F.

In the current example, the answer is 2 because 2 rows - rows 1 and 2 - contain entries for at least one pair of columns. Specifically, row 1 has entries for all column pairs, while row 2 contains entries for the A-D column pair - but this alone suffices the criterion. If row 4 had an entry in column E then this too would meet the criterion and the answer would change to 3).

I have already thought about how to count pairs of entries for two columns using the following array formula:

=SUM(IF(A1:A4<>"",IF(D1:D4<>"",1,0)))

However, I am having difficulty extending this to multiple columns with an OR type feature that only counts one pair of entries per row, even if that row contains more pairings.

I asked a similar question here which might be useful to look at.

I would appreciate your thoughts on this.

Aucun commentaire:

Enregistrer un commentaire