Hi,
In the table below, I have three columns, where column A contains Order number, column B contains Item Category which is one of three possible actors: A, B or C, and column C contains Amount.
Please note that there can be several orders with the same Order number, and either the same or different Item Category.
From this table, I would like to derive the sum of all orders which have two different outcomes in Item Category.
In addition to summarizing the Amount on these orders, I would also like to count the number of unique Order number for the orders that are included in the sum.
For instance, Order number 1 has two different Item Categories, A and B, the sum of these two rows would be included in the sum I am looking for.
Order number 3 has B in Item Category on both its rows, which means it would not be included in the summarization.
Order number 7 has only one row, and would not be included in the summarization, regardless of outcome in Item Category.
Is there any way to do this? I feel like I have been beating my head at this for hours by now.
In the table below, I have three columns, where column A contains Order number, column B contains Item Category which is one of three possible actors: A, B or C, and column C contains Amount.
Please note that there can be several orders with the same Order number, and either the same or different Item Category.
From this table, I would like to derive the sum of all orders which have two different outcomes in Item Category.
In addition to summarizing the Amount on these orders, I would also like to count the number of unique Order number for the orders that are included in the sum.
For instance, Order number 1 has two different Item Categories, A and B, the sum of these two rows would be included in the sum I am looking for.
Order number 3 has B in Item Category on both its rows, which means it would not be included in the summarization.
Order number 7 has only one row, and would not be included in the summarization, regardless of outcome in Item Category.
Is there any way to do this? I feel like I have been beating my head at this for hours by now.
| Order No. | Item Category | Amount |
1 | A | 58 |
1 | B | 223 |
2 | B | 69 |
2 | C | 46 |
3 | B | 169 |
3 | B | 38 |
4 | C | 468 |
5 | C | 53 |
6 | C | 120 |
6 | C | 77 |
6 | C | 63 |
6 | C | 10 |
6 | C | 63 |
7 | C | 154 |
8 | C | 127 |
9 | C | 27 |
9 | C | 13 |
10 | C | 5 |
10 | C | 89 |
10 | C | 87 |
11 | A | 57 |
11 | C | 28 |
12 | C | 71 |
12 | B | 135 |
