CODEWITHSUNDEEP
excelexcel-formula
Shenanigator
Shenanigator

Reputation: 1066

SUM columns on the same sheet based on conditions or SUMIFS from another sheet

Here is a small sample table

+--------+-------+--------+
| COL 1  | COL 2 | COL 3  |
+--------+-------+--------+
| abc123 | Total |        |
+--------+-------+--------+
| abc123 | cat1  | 100.00 |
+--------+-------+--------+
| abc123 | cat2  | 200.00 |
+--------+-------+--------+
| def123 | Total |        |
+--------+-------+--------+
| def123 | cat1  | 100.00 |
+--------+-------+--------+
| def123 | cat2  | 200.00 |
+--------+-------+--------+

In COL 3, IF COL 2 is "Total" I need to SUM everything in COL 3 for each row in COL1 that is the same. (EG. COL3 Total row should be 300.00 for abc123 and then 300.00 for def123) Otherwise if COL 2 is NOT "Total" I need to do SUMIFS('Sheet3'!N:N,'Sheet3'!A:A,Sheet2!A473,'Sheet3'!Q:Q,Sheet2!Q473)*Sheet4!$U$2)

How can I can I accomplish the first part of the SUM?

Edit:

I think my example is too rigid and appears like it is set.

Let me see if I can explain in more fluid terms. I will have to describe this some what in database terms. All of the columns are on one sheet for the purposes of the "Total" portion.

COL 1 is my partition. Each of the "ID's" in COL 1 consists of 57 rows. Within 1 of those 57 rows is "Total" in another column, in the example that is COL 2.

So I have a large table that in COL 1 there are say 5 different ID's with 57 rows for each ID resulting in 285 rows.

Now I had a sorting function that would likely make this whole thing easier, but that function is crashing excel and not sorting both required sorts ( https://techcommunity.microsoft.com/t5/excel/sort-function-causes-a-crash-and-does-not-perform-secondary-sort/m-p/1477123#M66205 )

I suppose if I can get the sorting function to stop crashing excel this becomes slightly easier as then "Total" is consistently placed in row 2, 58, 116, etc. and I can add up everything below it. Right now, because that sort doesn't work, I have to add up everything from COL 3 that is NOT assigned to "Total" in COL 2 and has the same ID in COL1.

So in the table above abc123 is 3 rows and I need to add up the two rows that are not total for abc123 and have the formula spit out 300 into COL 3 for total.

Then def123 needs the same treatment.

Here is the tough part: the sorting is inconsistent because the data comes from a Redshift query so it is random for each ID. The IDs themselves are in random order. I think I can get the sort for COL 1 to work without crashing excel, but the secondary sort with the custom order is crashing it.

Upvotes: 0

Views: 58

Answers (1)

Chronocidal
Chronocidal

Reputation: 7951

One way to avoid the Circular Reference error when trying to Total a column is to use two Sums, one above and one below.

So, assuming that your Columns 1, 2 and 3 are A, B and C, and that data starts in Row 2 (Row 1 being a header), you need the Sum of cells above the current row:

SUMIFS(C$1:C1, A$1:A1, A2)

Plus the Sum of the cells below the current row:

SUMIFS(C3:INDEX(C:C, 1+COUNTA(A:A)), A3:INDEX(A:A, 1+COUNTA(A:A)), A2)

(Note that this will actually terminate one row above and below the dataset)

Put this together with an IF statement:

=IF(B2="Total", SUMIFS(C$1:C1, A$1:A1, A2) + SUMIFS(C3:INDEX(C:C, 1+COUNTA(A:A)), A3:INDEX(A:A, 1+COUNTA(A:A)), A2), EXISTING_FORMULA_HERE)

Alternatively, you could try writing an Array Formula to calculate the SUM directly, a bit like when using multiple conditions in a MATCH, something like this: (not enough information in the question to do this exactly)

=SUMPRODUCT('Sheet3'!N:N*(COUNTIFS(A:A,'Sheet3'!$A:A)>0)*(COUNTIFS(B:B,'Sheet3'!$Q:Q)>0))

(Sum of Sheet3!N:N when a row exists in the current sheet that matches columns Sheet3!A:A in Column A and Sheet3!Q:Q in Column B)

Note that working on Entire Columns with Array Formulae is quite slow, so you may want to limit those just to the Used Range

Upvotes: 1

Related Questions