Sum uniquevalues in dependence to another column

Hi guys,

I am sitting on this for a long time now and hope somone can help me. Please find ma file attached.

What I need in column F is the following:
- look at Pers no (A)
- look at Month/Year (C)
- in dependence of these two, take each value from column D once and sum column E

Result is a single PersNo should have one Value per Month.

I hope this is somewhat clear.

Please find the file attached.

Thanks a lot!

Try

Formula:

Please Login or Register  to view this content.

Change ranges according your need

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Thanks a lot, that was already very helpful.

How can I include the personnel number in this?

----------

Hi all- sorry I'm late to this, but I noticed the worksheet is slow to calculate. If your data is sorted [keys A, C], you can reduce calculations with a test. Paste this ARRAY FORMULA in F2 and copy down:

Please Login or Register  to view this content.

NOTE: The formula above assumes 1 person per budget. If you can't count on that, use this instead:

Please Login or Register  to view this content.

Originally Posted by JasXel

I tried your suggestion, but got a bit lost on the F1. Please tell me why are you returning column F if A and C are like the cell above? In first cell it worked, but dragging the formula down didn't give correct results.

1) It's an ARRAY FORMULA. Make sure you press CTRL+SHIFT+ENTER after pasting or editing the formula in the formula bar.
2) Your sample file had workbook calculation set to Manual. Make sure it's changed to Automatic (Files> Options> Formulas> Calculation Options)

The test works as follows: if this row and the previous row match in columns A and C, then they will also share the same result in THIS column (F). Therefore, if a match is found, don't bother to re-calculate, just copy the previous cell.
In the (sorted) sample data, this reduced the need for calculation to just 149 of 4776 cells - about 3% of the total.

Ok, I tried altering the formula to process 187,000 rows. It seems there's some limitation involved, because row 1 returned 0. And since that many rows would take a LONG time to process anyway, I decided to try a different tack. I REDUCED the number of rows processed to just 300, but relatively addressed so that the range moves with the formula as it's copied downward. Now assuming the data is sorted as recommended and no group has more than 300 qualifying entries, the first cell in a group calculates the correct total, and the remaining cells in the group copy its result serially. With no other formulas on the spreadsheet, the following ARRAY FORMULA took about 40 seconds to calculate after being copied to 186,265 rows comprising 5811 groups.

Please Login or Register  to view this content.

This is your formula in G2:

Please Login or Register  to view this content.

Problems:
1) You added a column, but didn't change F1. This must be G1 to read the PREVIOUSLY calculated value DIRECTLY above (if available).
2) I thought the whole idea was to add a single instance of every budget within the subset {specified person, specified month}.
You seem to be attempting to specify the budget too with this : D2=D1. If it is your intention to add a single instance of every AMOUNT within the subset {specified person, specified month, specified BUDGET}, the FREQUENCY function will require different inputs (NOT SHOWN).

This seems to achieve you original goal:

Please Login or Register  to view this content.

Note that I adjusted the lookup to 3400 rows. This took about 5 minutes to calculate using my faux data, but it should be faster with the actual data, assuming fewer groups (as implied by that 3400-row requirement).

JasXel thank you for the upload. I've been watching this and couldn't understand the mission.

I took a different approach on limiting the ranges to process. There is no mention of helper columns so far. If they are acceptable this uses 3.

Applied to the original 4777 row file:
This one returns the lower and upper row boundaries of each set. In A1:B1 and filled down. It sets the ranges dynamically.

Formula:

Please Login or Register  to view this content.

This one in column J returns the goals.

Formula:

Please Login or Register  to view this content.

The final formula in column I

Formula:

Please Login or Register  to view this content.

Hi Dave- I couldn't get your formulas to work with 180,000 rows, but you did inspire me to try a different helper column approach. Using the techniques for reducing calculation mentioned in posts # 8-9, I employed COUNTIFS to get the exact size of each group (paste in I2 and copy down):

Please Login or Register  to view this content.

Then I used OFFSETs to process the exact range in the main formula, again doing the full calculation just once per group. (ARRAY FORMULA in G2 and copied down):

Please Login or Register  to view this content.

This took 12-15 seconds to process 180,000+ rows sorted into 5,811 groups.
...And yes, I know OFFSET is volatile, but it's also fast. I think it's the right function for this.

Edit Never mind my questions about OFFSET below. I re-read your last post. I would dearly love to use it, too. The volatile part doesn't seem to be much of an issue as OP plans to copy/paste values in the end.

@ lee,

Thanks for the backup. I've been reviewing my formulas and approach. This set is much faster and more efficient than my previous. I've only tried it on the 4777 rows. The helpers only execute at change of Person No./Date/Budget.

The helper in A:B is now

Formula:

Please Login or Register  to view this content.

The helper under Goal is

Formula:

Please Login or Register  to view this content.

The final formula can now be.

Formula:

Please Login or Register  to view this content.

The first two helpers execute in 1.8 sec. The other helper in just under 3/100 of a second and the final formula just under 3/1000 of a second.

I am curious to see how it does on mega data. In the mega data you have would executions be few enough to get away with using OFFSET? I'd like to drop that clunky INDEX():INDEX() construction?

What is odd is the file size is quite a bit larger now.

Originally Posted by leelnich

This seems to achieve you original goal:

Please Login or Register  to view this content.

Note that I adjusted the lookup to 3400 rows. This took about 5 minutes to calculate using my faux data, but it should be faster with the actual data, assuming fewer groups (as implied by that 3400-row requirement).

So this formula actually worked!! Yay! Thanks a lot. It took a long time to calculate though, I stopped monitoring it at some point but it was about 30-45 minutes.

I will try the other approaches and let you know if they were quicker.

I do intend on pasting the values at the end just (just keeping formula in first rough, to know what I actually calculated) because I have other array formulas in it as well and keeping it all in slows everything down. However, potentially I will have to play with the data at some point and the quicker the formula is, the better...

Originally Posted by leelnich

...... I employed COUNTIFS to get the exact size of each group (paste in I2 and copy down):

Please Login or Register  to view this content.

Then I used OFFSETs to process the exact range in the main formula, again doing the full calculation just once per group. (ARRAY FORMULA in G2 and copied down):

Please Login or Register  to view this content.

This took 12-15 seconds to process 180,000+ rows sorted into 5,811 groups.

lee, Great idea, but I can't get it to work at my end. Is there any chance you could upload a zip file of what you have? I'd love to see this in context. I'm missing something.

Thanks,
Dave

Hi all- here's the data I've been testing. Enable macros when asked, then click the Blue button. The macro will extend the existing data (with new ID numbers) down to ~182,000 rows, then add the formulas and calculate. Dave, I tried your formula - it's actually still in the macro code - but it was taking too long. Feel free to fiddle with it, and please let me know if you wish to discuss further... -Lee

@ Lee

Sorry to hear that, but thanks for the upload. Did you try the new formulas from post #15?

Dave

You're most welcome, thank you for the rep! If attempted, how long did the formulas from post #14 (repeated in #19) take to calculate?