SUMIFS formula in same column as the sum range (Possible?)

[ChristianEllehammer]

I've been using this awesome forum for many years, getting all of my Excel questions answered by the awesome forum members and Excel gurus, but now I'm having a task to which I cannot seem to find an already existing topic/solution for, thus this is my first post.

I'm having (almost) the same issue/question as khadert in this topic: http://www.mrexcel.com/forum/excel-questions/452723-sumproduct-circular-reference-help.html

Let me explain:

I want to SUM a column (column C) depending on two text criteria.
First criteria is in column A, and second criteria is in column B.
The formula HAS to be in the same column as the SUM range (column C), thus I need to exclude the rows of the criterias in the sum range column (C).

I know to use SUMIFS, but using this returns 0 as it makes a circular reference to the cell in which the SUMIFS formula is (C1 and C5).

Would be nice if the SUMIFS-formula could find the criteria cells with OFFSET, finding the criterias one and two rows left of the formula cell. I need this as I'm filtering the complete list, thus having 'simple' cell references would mess up the SUMIFS-results.

[TABLE="width: 900"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name1[/TD]
[TD]Total[/TD]
[TD]FORMULA WHICH SUMIFS C:C with criterias in A1 ("Name1") and excludes all rows with the criteria of "Total"
Should be = 3 (SUM of C2:C4)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Name1[/TD]
[TD]Sub1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Name1[/TD]
[TD]Sub2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Name1[/TD]
[TD]Sub3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Name2[/TD]
[TD]Total[/TD]
[TD]FORMULA WHICH SUMIFS C:C with criterias in A5 ("Name2") and excludes all rows with the criteria of "Total"
Should be = 6 (SUM of C6:C8)[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Name2[/TD]
[TD]Sub1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Name2[/TD]
[TD]Sub2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Name2[/TD]
[TD]Sub3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Etc...[/TD]
[TD]Etc...[/TD]
[TD]Etc...[/TD]
[/TR]
</tbody>[/TABLE]


I hope that I have been clear about my issue, else please let me know.

THANK YOU!

 

[MarcelBeug]

Hi, did you explore the subtotal option on the data tab? This might give you exactly the result you are looking for.

 

[ChristianEllehammer]

Subtotal is unfortunately not an option as I do a lot of filtering and sorting which messes up to subtotals.

 

[MarcelBeug]

This works for me in C2:

=SUMIFS(C:C,A:A,A2,B:B,"<>Total")

No circular reference whatsoever.