0
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 Desired Result
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.
Note: The values in Column B
are unique to their first letter;
there are no Aardvark
, Butterfly
, or Caterpillar
entries.
A B C Desired Result 1 17 Antelope 101 502 2 17 Badger 201 3 17 Cougar 301 4 17 Dingo 401 5 17 Elephant 501 6 42 Zebra 102 504 7 42 Badger 202 8 42 Cougar 302 9 42 Dingo 402 10 42 Yak 502 11 83 Koala 103 506 12 83 Badger 203 13 83 Cougar 303 14 83 Fox 403 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 95 Dingo 204 22 95 Cougar 304 23 95 Badger 404 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.
Which version of Excel are you using? – snoopen – 2015-06-11T23:38:18.237
Actually version is not so much an issue now I fully understand your requirements. I think you'd be better of creating a custom function in VBA if you know how. Otherwise if you have a limited and known set of values that will appear in column B you might be able to work something out with an Array Formula or Sumproduct. Note that Sumprodcut is an not a CSE formula but can be used to do array calculations similar to CSE formulas. – snoopen – 2015-06-11T23:49:59.657
Would it be correct to think that you are looking for a one-line formula as an acceptable answer? BTW, I actually prefer the chart to an image, at least you can copy and paste a chart into excel. With the aid of "text to columns" you can start testing answers without having to retype the data. – Clif – 2015-06-12T00:35:49.303
Ditto; don’t apologize for not posting an image. As Clif says, it’s easier on us if we have text that we can copy and paste. However, I understand English fairly well, and I understand Excel fairly well, and, after reading it three times, I’m still struggling to comprehend the question. Am I correct in saying that you want three results — one for
A
=1, one forA
=2, and one forA
=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 eachA
value)? Etc. – Scott – 2015-06-12T03:53:13.650@snoopen: Excel 2010. I feel like I can use the CSE formula but I am unable to write out the logic. – EA1234 – 2015-06-12T12:54:53.097
@Clif: Yes, a one-line formula is exactly what I'm looking for - I'd really appreciate an example formula that I can use. – EA1234 – 2015-06-12T12:59:27.100
@Scott: Yes, ultimately I want to be able to use the formula and apply it to generate 3 results, one for each Column A # (1-3) - but an example formula for 1 result (e.g. Column A #1) will work. This is just an example of a much larger spreadsheet that I'm using. Once I have this figured out, applying the similar logic to a larger set of data won't be too difficult. The number of groups of rows and columns can change.
To write out my thoughts to help understand:
For Column A #1 result ----- If column A # is 1 and if column B criteria are also present in column A # 2 and 3, sum Column C #s – EA1234 – 2015-06-12T13:06:22.177
I rewrote the question, almost from scratch, to reflect my understanding of your intent, which is probably flawed. Please [edit] the question. If I’m totally confused, delete what I wrote. If I’ve nailed it, delete your original version. If it’s somewhere in-between, keep the best of each and burn the rest. – Scott – 2015-06-13T05:03:51.057