Excel Count/Sum Based on Multiple Criteria for Same Row

Question

I have a relatively simple problem which I am getting stumped on - perhaps it is this brain fog from Covid. I'll try my best to explain the problem.

Here is a simulated dataset:

    A   B   C   D   E   F   G   H   I   J   K   L   M   N
1   X1  X2  X3  Y1  Y2  Y3  X1  X2  X3  X1  X2  X3  Ct  St
2   1   2   0.2             0   2   0.5 1   2   0.1 2   0.3
3   1   2   0.3             1   1   0.2             1   0.3
4   1   2   0.6 1   2   0.1                         1   0.6
5   1   2   1.1                 2   0.7 1       0.5 1   1.1

A-N reflects the column names while the first column (1-5) reflects the row names in Excel.

Each column has been labelled as either X (e.g., male) and Y (e.g., female). There are three characteristics for male (X1, X2, X3) and three characteristics for female (Y1, Y2, Y3). We can think of adjacent columns as belonging to a trait (e.g., X1, X2, and X3 in columns A, B and C form a set of male characteristics for trait 1; X1, X2, and X3 in columns G, H and I form a set of similar characteristics but for trait 2, etc.).

For each row, I would like to calculate a count total (Ct, see column M) and sum total (St, see column N) based on a set of conditions.

Count total: Count the number of male (X) traits that feature a "1" for X1 and "2" for X2, giving a 'count total'.

Sum total: Sum the X3 values over male (X) traits that feature a "2" for X2, giving a 'sum total'.

I have manually calculated the count totals and sum totals for each column to make these definitions clearer. In row 1, there are two traits that fulfil the count total criteria (Ct = 2), whereby their X1 values = 1 and X2 values = 2. Notice that while the X2 value in column H qualifies (X2 = 2), X1 in column G is not equal to 1, so it is not counted. Furthermore, we only sum the X3 values for traits 1 and 2 (e.g., X3 in Column C and X3 in Column L), giving us a total of 0.3 (0.2 + 0.1).

The formulae should ignore sets of values that qualify but are for female traits (e.g., see row 3) and should work across missing values (e.g., in col J, row 4, X1 is missing, so it cannot be counted, even if X2 in col K row 4 features a qualifying value of 2).

I hope that makes sense.

My instinct was to use a SUMPRODUCT formula, but I am struggling to integrate the two conditions, e.g., for each row:

=SUMPRODUCT(((A1:L1="X1")*(A2:L2=1))*((A1:L1="X2")*(A2:L2=2)))

Any guidance would be much appreciated.

Answer 1

I haven't checked this thoroughly, but suggest for Ct

=SUMPRODUCT((A$1:J$1="X1")*(A2:J2=1)*(B$1:K$1="X2")*(B2:K2=2))

and for St

=SUMPRODUCT((A$1:J$1="X1")*(A2:J2=1)*(B$1:K$1="X2")*(B2:K2=2)*(C$1:L$1="X3")*C2:L2)

copied down.