Can some please explain this formula? I know what it does, just don't know why!

[Jarodjp42]

=SUM(IF(IF(MATCH(Table1[Ivoice],Table1[Ivoice],0)=ROW(Table1[Ivoice])-ROW(Table1[[#Headers],[Ivoice]]),(ROW(Table1[Ivoice])-ROW(Table1[[#Headers],[Ivoice]])),0)<>0,Table1[Totals],""))

Can someone break it down and explain the meaning behind the inputs please? What does the ROW function do in this formula?
Thank you!!!

Here is the table of data I used.

[TABLE="width: 163"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Ivoice[/TD]
[TD]Totals[/TD]
[/TR]
[TR]
[TD]INV-000014[/TD]
[TD="align: right"]128[/TD]
[/TR]
[TR]
[TD]INV-000014[/TD]
[TD="align: right"]128[/TD]
[/TR]
[TR]
[TD]INV-000021[/TD]
[TD="align: right"]223[/TD]
[/TR]
[TR]
[TD]INV-000021[/TD]
[TD="align: right"]223[/TD]
[/TR]
[TR]
[TD]INV-000105[/TD]
[TD="align: right"]855[/TD]
[/TR]
[TR]
[TD]INV-000105[/TD]
[TD="align: right"]855[/TD]
[/TR]
[TR]
[TD]INV-000105[/TD]
[TD="align: right"]855[/TD]
[/TR]
[TR]
[TD]INV-000105[/TD]
[TD="align: right"]855[/TD]
[/TR]
[TR]
[TD]INV-000110[/TD]
[TD="align: right"]107[/TD]
[/TR]
[TR]
[TD]INV-000110[/TD]
[TD="align: right"]107[/TD]
[/TR]
[TR]
[TD]INV-000110[/TD]
[TD="align: right"]107[/TD]
[/TR]
[TR]
[TD]INV-000117[/TD]
[TD="align: right"]1546[/TD]
[/TR]
[TR]
[TD]INV-000117[/TD]
[TD="align: right"]1546[/TD]
[/TR]
[TR]
[TD]INV-000117[/TD]
[TD="align: right"]1546[/TD]
[/TR]
[TR]
[TD]INV-000118[/TD]
[TD="align: right"]998[/TD]
[/TR]
[TR]
[TD]INV-000118[/TD]
[TD="align: right"]998[/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

The formula is summing the values in your column Totals for each unique item in your column Ivoice.

[I had a Table already named Table1 so I had to change it to table4, but I'm sure you get it.]

MATCH(Table4[Ivoice],Table4[Ivoice],0)
This function reports {1;1;3;3;5;5;5;5;9;9;9;12;12;12;15;15}, corresponding to the matches which have a unique Item. So you can see that the first two items are equal, and therefore MATCH reports a 1 for each.

The logical test in the inside function IF reports the rows that have a unique item by first creating an array of the number of rows in your column Ivoice, which is done by the double-use of function ROW, like this:
ROW(Table4[Ivoice])-ROW(Table4[[#Headers],[Ivoice]]), which evaluates to {2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}-{1}, which then evaluates to the expected {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}

So the inside logical test MATCH(Table4[Ivoice],Table4[Ivoice],0)=ROW(Table4[Ivoice])-ROW(Table4[[#Headers],[Ivoice]]) evaluates to {TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE} which represents the items we want to grab. That becomes the logical test for the outside function IF, which when evaluated reports only the unique items and their values from the column Totals. That in turn evaluates to {TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}, after which the outside function IF reports either the unique value or a blank.

Then function SUM adds it all together after having been invoked as an array formula with CTRL+SHIFT+Enter.
=SUM({128;"";223;"";855;"";"";"";107;"";"";1546;"";"";998;""}), which evaluates to 3857.

I hope that was clear. The ROW stuff is a common technique used to generate an array from 1 to however many rows are in the relevant array. Many times, it looks like this: ROW(A20:A40)-ROW(A20)+1 (which of course evaluates to {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21}), but in your formula the second ROW part went one row above the top item and avoided the +1 at the end.

 

[Jarodjp42]

Thank you for the response. I am wondering why the <>0 is in the formula in the If true field of the outside IF function?

 

[barry houdini]

Seems like you have an IF too many in there - this version should give the same result

=SUM(IF(MATCH(Table1[Ivoice],Table1[Ivoice],0)=ROW(Table1[Ivoice])-MIN(ROW(Table1[Ivoice]))+1,Table1[Totals]))

confirmed with CTRL+SHIFT+ENTER