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

Question

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.

Should that read 'if COL 2 is NOT "Total"', instead of 'if COL 3 is NOT "Total"'? — Chronocidal
I am presuming that you have already tried using a straight SUMIFS, and run into the issue with Circular References? If so, that (i.e. "what have you already tried") would have been good information to include in the question. — Chronocidal
Would you mind rephrasing your problem statement or share the output table for better understanding ? — bp41
Also, is this code on Sheet2, Sheet3, Sheet4 or on a fourth sheet (Sheet1?) I'm not quite sure what the relative references should be in that final formula... — Chronocidal
@Belle the output table would just have a formula in COL3 where COL2 = Total that SUMS COL3 for each COL1 "ID" so COL3 row 2 would be 300.00 and COL3 row 5 would be 300.00. If I really need to make a table for that I suppose I can. Typo fixed — Shenanigator

Chronocidal Chronocidal · Accepted Answer · 2020-06-19T16:27:42

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

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

1 Answers