Sum duplicate values returned by array formula INDEX(MATCH(LARGE(IF)))

Hi all- The solution in post #5 does not fulfill this requirement:

Originally Posted by TFiske

...2) for those locations with multiple accounts,... I would like it to sum the consumption and return the location only once.

In the attached file, I have employed a named formula - basically a virtual column - which adds the first tabular position of each location (divided by 10000) to a period- and location-specific SUMIFS. This yields PeriodSums, an array of totals differentiated by location:

Please Login or Register  to view this content.

The next formula returns the location having the k-th largest consumption for the specified period, with k indicating position in the Top Ten table. Note that PeriodSums contains multiple (duplicate) entries per location. The construction IFNA((FREQUENCY(PeriodSums,PeriodSums)>0)*PeriodSums,0) effectively removes the duplicates, producing an array for the LARGE function to process. The result is then "unpacked"; the integer portion is discarded, and the remainder multiplied by 10000 to give the INDEX function the tabular position of the location name. (Locations with 0 consumption return ""). Paste this ARRAY FORMULA* in A6 and copy down:

Please Login or Register  to view this content.

... and this simple SUMIFS in B6, copied down:

Please Login or Register  to view this content.

Please note, column A is a complex formula. It requires about 10 seconds to calculate new inputs.

*Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.

Debatable. I really hate any formula that takes longer than 4 seconds to calc, but you may be able to live with slow if the inputs rarely change. I'm trying a different approach, I'll get back to you...

So, let me see if I can understand what your first formula is doing?

PeriodSums is operating in a similar way to CONCATENATE in that it generates an "address", for lack of a better word, for each row in the Consumption Records table.

Where you loose me is in the 2nd formula. I understand that INT would reduce each value generated by PeriodSums to a unique Integer, but I don't see how that relates to "Location" as the [value if true] part of the IF statement.

I can see how MOD would then "reattach" a particular remainder to the unique Integer generated by the INT function and arrive at a unique value. Am I wrapping my head around this or am I totally off track, ;-)?

Finally, I have used FREQUENCY a few times but can you explain a little more how it removes the duplicates. Thank you, thank you. This is fascinating!

OK, this is calculating in under a second for me. I instituted a 2-column helper area (columns G:H in the sample, but you can move them elsewhere). Column H is a list of locations with duplicates removed (which allows us to drop the FREQUENCY function). Column G is like PeriodSums - it appends each location's tabular position to its SUMIFS value as a means of differentiation- but it's much more efficient as a worksheet formula calculated just once per unique entry.

The helper columns are named Psums (= Sheet2!$G$2:$G$201) and Locations (= Sheet2!$H$2:$H$201)

The formula to list unique Locations - paste in H2 and copy down (I allowed for 200 locations in the sample):

Please Login or Register  to view this content.

... and for differentiated Psums - paste in G2 and down:

Please Login or Register  to view this content.

Now for the main Top Ten formula, Psums can be plugged straight into the LARGE function:

Please Login or Register  to view this content.

...while we can skip re-calculating the SUMIFS, and instead just lookup the value.

Please Login or Register  to view this content.

This is pretty amazing. I'd give you more rep, but it says I need to "spread some rep around" first.

I think I have a better grasp of what you're doing with seeing the various parts of the formula broken out.

One question, there is an occasional anomaly in the data set where the consumption values for an account are negative, I'm sure you noticed. This is a quirk of how the Utilities Department sometimes edits consumption and not anything I can control. Obviously, those will never appear in the top 10 list, just wondering how this approach might be affected by negative values.

I can see several applications for this approach across some other projects I'm working on.

I'll implement your solution in my working sheet on Monday. I will mark this as solved.

I may have some questions re. your formulas and this tabular positioning you're using,
can I contact you privately through the forum or would you prefer I stick to the thread?

Either way, Thank you, this is great!

Yes, I noticed the negative adjustments. Here's CONSUMPTION for the month of OCT 2017, with zero and blank amounts filtered out:

Untitled.png

As you can see, the top 3 lines all reference DALBEY MEMORIAL PARK, with consumptions of -1, 5, and 7. As written, the formulas incorporate negatives in the total, so DALBEY's OCT total is 11, but you could add another criteria to the SUMIFS to filter them out.

Originally Posted by TFiske

...I may have some questions re. your formulas and this tabular positioning you're using,
can I contact you privately through the forum or would you prefer I stick to the thread?

You may P.M. me and/or stick with this thread for further amplification, but the best approach if you have new requirements is to start a new thread (and perhaps include a link to this one, if related). That will get fresh eyes and minds on it.

and...You're welcome, thanks for the rep! Note: awarding rep to ALL who gave their time and effort to your request is always appreciated!

IMHO To build complicated formulas is very exiting, but in his case one should use a simple Pivot table
or formulas, but quite simple ones

Hi @tim201110. I took a look at your submissions. I had two issues with the Pivot Table solution. 1) the entries should be sorted automatically and 2) the negative values mentioned in posts #14-15 aren't included in the totals. Since I don't know Pivot Tables (yet), I can't say how easy these would be to fix.

On the other hand, I really liked your Formula-based solution. As fast as my approach, with less impact on calculation speed when new records are added to the CONSUMPTION RECORDS table. I took the liberty of making a few changes. Please see the attached file for a complete description.
Nicely done! - Lee