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

Jarodjp42

New Member
Joined
Nov 21, 2017
Messages
35
=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]


 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.
 
Last edited:
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top