Question: I'm trying to sum up all the entries based on several specific conditions.

The goal is to count all entries that have a 'yes' ticked in them in cells J or K or L or M and have '1st' in H

This is the formula I'm currently using:

```
=SUMPRODUCT((('J2:J100="yes") + (K2:K100="yes") + (L2:L100="yes") + (M2:M100="yes")) * (H2:H100="1st"))

```

This almost works.

The problem is that I want to count a record only once if he has a yes for any of the 4 cells (J,K,L,M) and not multiple times. So for example, if a single record has 'yes' marked in both cells K and L and has 1st in H he will be counted twice instead of once.

This is because the SUMPRODUCT function would evaluate to 0 + 1 + 1 + 0 \* 1 which is = 2

I'm thinking if there's a way to convert the first part of the function I've written to be transformed in to a boolean so that any positive value is tread as a 1 and any negative is treated as a False it would solve my problem.

Answer: 
Assuming the duplicate values are contiguous !
----------------------------------------------

---

A perl script that does the job.

Not tested on big file!

```perl
#!/usr/bin/perl
use strict;
use warnings;

my $file = 'file1'; # path to input file
# read the input file in memory
open my $F, '<', $file or die "unable to open '$file': $!";
my @list = <$F>;chomp @list;
# delete all . - _ from each line and add this new string in the array for comparison
my @res = map {my $tmp=$_; tr/._-//d; [lc$_,$tmp] } @list;
# memoize the first values
my $prev_tst = $res[0][0];  # contains the string without punctuation
my $prev_orig = $res[0][1]; # contains original string
# loop on other values
for my $ind (1 .. @res-1) {
    my ($tst, $orig) = ($res[$ind][0], $res[$ind][1]);
    # te string without punctuation is the same as the previous
    if ($tst eq $prev_tst) {
        # if the previous original value contains dot
        if ($prev_orig =~  tr/.//) {
            # delete it
            undef $res[$ind-1];
        # if the current original value contains dot
        } elsif ($orig =~ tr/.//) {
            # delete it
            undef $res[$ind];
        }
    }
    # memorize value for next step
    $prev_tst = $tst;
    $prev_orig = $orig;
}
# write result to result file
my $result = 'result_file'; # path to result file
open my $R, '>', $result or die "unable to open '$result': $!";

for (@res) {
    next unless defined $_; # skip undifned values
    print $R $_->[1],"\n";
}

```
Something along the lines of

```
sed 's/\./-/g; s/__*/-/g' /path/to/infile | sort -u > /path/to/outfile

```

should do the trick