# See below for rewrite.

Please refer to the data below for the question. The very top A B C are columns and the far left numbers 1 through 15 are rows. The rest are cells.

```
A B C
1 1 A 100
2 1 B 200
3 1 C 300
4 1 D 400
5 1 E 500
6 2 Z 100
7 2 B 200
8 2 C 300
9 2 D 400
10 2 Y 500
11 3 K 100
12 3 B 200
13 3 C 300
14 3 D 400
15 3 J 500
```

I want to sum #s on Column C using the Columns A and B as criteria: **Sum the Column C numbers only if Column A is 1 and its Column B components (A, B, C, D, E) of Column A = 1 are also on Column A = 2 and Column A = 3 (highlighted in bold; B, C, D)**.

For example:

The desired output for Column A = 1 should be 900 (B+C+D) since only B, C, D are also included for Column A = 2 and Column A = 3

A (Cell B1), E (Cell B5) for Column A = 1 are excluded because they are not duplicated for Column A = 2 and Column A =3"

I thought of using Array Formula (CSE) and SUMPRODUCT to create a sort of COUNTIF rule but lost myself in forming a proper logic.

## Proposed re-write:

Please refer to the data below for the question. The very top `A`

, `B`

, `C`

,
and

are just the column names,
and the far left numbers 1 through 24 are the row numbers.
The rest are cells with data. The data are not actually formatted
(bold and italic); the formatting below is there just to highlight cells
that are discussed in the text.**D**esired Result

^{Note: The values in Column B are unique to their first letter;
there are no Aardvark, Butterfly, or Caterpillar entries.}

A B CDesired Result 1 17 Antelope 101 502 2 17Badger201 3 17Cougar301 4 17Dingo401 5 17 Elephant 501 6 42 Zebra 102 504 7 42Badger202 8 42Cougar302 9 42Dingo402 10 42 Yak 502 11 83 Koala 103 506 12 83Badger203 13 83Cougar303 14 83403 15 83 Jaguar 503 16 83 Llama 603 17 83 Moose 703 18 83 Ocelot 803 19 83 Panther 903 20 95 Tiger 104 708 21 95FoxDingo204 22 95Cougar304 23 95Badger404 24 95 Weasel 504

This is just the beginning of a much larger spreadsheet.
Column `A`

contains a list of non-unique values (numbers),
and the worksheet is sorted by Column `A`

(or at least grouped),
so all the `17`

s are in consecutive rows, all the `42`

s are together, etc.
In the real file, there are way more than four distinct values in Column `A`

.

The groups of distinct values in Column `A`

are the key to this problem.
One way to look at it is that a few of the values in Column `B`

are present
in *every* group of Column `A`

values.
They are highlighted in (non-italic) bold in the data above:
`Badger`

(in cells `B2`

, `B7`

, `B12`

, and `B23`

) and
`Cougar`

(in cells `B3`

, `B8`

, `B13`

, and `B22`

).

Now, for each unique Column `A`

value,
I want the sum of the values in Column `C`

that are in the same row
with a Column `B`

value that is bold;
i.e., a `B`

value that occurs in every group.

For example:

The desired output for the first group, in which Column `A`

= 17,
is 502 (shown in cell `D1`

), because rows 2 and 3 are the rows in "Group 17"
that contain `Badger`

and `Cougar`

, and `C2`

+`C3`

is 502.
Similarly, `D6`

= `C7`

+`C8`

, `D11`

= `C12`

+`C13`

, and `D20`

= `C23`

+`C22`

.

If cell `B14`

were changed from `Fox`

to `Dingo`

,
then it would be the same as `B4`

, `B9`

, and `B21`

(all currently shown in italics).
Therefore, they would become bold, and so `C4`

, `C9`

, `C14`

, and `C21`

would enter into the sums, resulting in Column `D`

values of 903, 906, 909, and 912.

I thought of using array formula (CSE) and `SUMPRODUCT`

to create a sort of `COUNTIF`

rule, but lost myself in forming the proper logic.

`A`

=1, one for`A`

=2, and one for`A`

=3? This isn’t the real, entire data set, is it? I presume there will really be more than three groups of rows (am I right)? Will there always be exactly five rows in each group (for each`A`

value)? Etc.3more comments