Formula to create unique list when another cell(s) are greater than a set parameter

[stuver]

Hello,

I am new to this website and was curious if something like the following would be possible. If any info is needed please let me know.

I attached the sheet so the example can be viewed. I am aware that an array is most likely going to be the answer, but I am not sure how how to nest it within others :x

What I would like to have help with: I would list 'names' with different 'monetary amounts' on the worksheet. Then, a formula would summarize the information by following these guidelines:
1. Only print 'names' whose values are greater than or equal to $200 for 'monetary amounts' and combine the monetary amounts as one line
2. Have a miscellaneous section that sums all values less than 200 that does not fall under guideline 1.
2b. Here is the catch, if there are 3 entries of the same name but with varying values that exceed 200 when summed, then it will print the name once and sum the 3 values as it would in guideline 1
3. The list should not have any duplicates or blank lines when created. It should be one continuous unique item list going from one to the next
3b. this is where i run in to the issue using the array. Due to how I have the array set up it is listing the name B when it should be the name C (since C's value is 10,000 while B is 100) but since B is the next unique item in the list it is listed
4. the name D is the example of what I would like to have happen for guideline 2b with the different D values being summed (and not appearing under miscellaneous)

I tried to explain this as best as possible. If anyone has questions please let me know, and I will get back to you as soon as possible. Thanks!

[FlameRetired]

stuver welcome to the forum. Thank you for uploading an Excel file. It helps.

Try array entering this formula in D24 and filling down until you get blanks. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

Then this slight change to the formula you had in E24 and filled down.

Please Login or Register  to view this content.

Then for amounts <200 this array formula in E23.

Please Login or Register  to view this content.

[stuver]

Thanks Dave, that does everything I wanted! I don't have much practice with arrays. Thank you for breaking out your explanations for what you did. I will review it more tomorrow and may have some questions regarding why you did what you did if you have time to answer.

[FlameRetired]

I'll do my best. Let me know.

[stuver]

Hi FlameRetired. Below are two additional concerns I'd like some help with. (Please see attached file, it is more so along the lines of what I want the final product to look like.)

Concerns:
1. Under the cash section summary for miscellaneous, it seems like the formula is taking each value of the name D and multiplying it by how many times the name appears (D appears 4 times so it is taking 100*4 + 100*4 + 100*4 +100*4 = 1,600, but since it is already adding the 100 + 100 + 100 +100 for each D name it is off by 1,200 in total (1,600 - 400).

1b. I am not sure if having the total for cash included in the array range is a bad idea (ie it may cause issues) but it seems like excel knows not to include it in the summary section.

2. I added a non-cash section summary using the initial array formula in example 1 (attached in 1st post). What would I need to do to make it alphabetize the list like the summary for cash does?

[FlameRetired]

1. Under the cash section summary for miscellaneous, it seems like the formula is taking each value of the name D and multiplying it by how many times the name appears (D appears 4 times so it is taking 100*4 + 100*4 + 100*4 +100*4 = 1,600, but since it is already adding the 100 + 100 + 100 +100 for each D name it is off by 1,200 in total (1,600 - 400).

An oversight on my part. My apologies. This array entered formula in E48 (Summary - Cash)

Please Login or Register  to view this content.

returns $900. This is puzzling as it includes the $200 way down in E23 (EXAMPLE CASH WITH BENEFITS). Was that the intent? I don't recall but that is included in the SUMIFS range $E$3:$E$32 from earlier.

For part 2 that formula I proposed sorts unique ORGANIZATIONS as Excel understands 'sorting'. The COUNTIF($D$3:$D$32,"<="&$D$3:$D$32) part of the formula takes care of the sorting (BTW there are spaces in 'A B C' that are considered), but it will sort this way:

A

A B C

AA

B

C

CC

And not as you indicate:

A B C

AA

A

B

CC

C

Is that acceptable?

[stuver]

Although using the above formula returns $900 shouldn't it return $1200?

ps. it is supposed to include the EXAMPLE CASH WITH BENEFITS amount as I want all the cash amounts summarized together.

[FlameRetired]

Yup! I missed it again. Geesh! My apologies.

This array formula returns $1200 as it should.

Please Login or Register  to view this content.

Previously I put the wrong reference in the 'if true' argument. I had $E$3:$E$32 when it should be SUMIFS($E$3:$E$32,$D$3:$D$32,$D$3:$D$32)

[stuver]

Not a problem. You have been an immense help and taught me a lot even though I don't completely understand it all or expect to use it again soon! The final question is that I used the countif function to have it sort based on organizations like you said but it looks at all the values, and then lists them in alphabetical order but skips any that are in-between. I attached the sheet for you to see what I mean (it goes apple -> ant -> skips everything in between and does not list baby, cat, dog etc (in the non cash section)).

[FlameRetired]

That looks like a mix of mine and another (shorter) formula that also alphabetizes unique items. Mine is array entered

Please Login or Register  to view this content.

What is looks like you are trying to do is this one

Please Login or Register  to view this content.

You'll notice that one can not start on row 1.

If alphabetizing unique items is all you want to do then I would go with the shorter one. I have just never found a way to also apply multiple conditions to that one.

[stuver]

Great! I will play with it next week since I am going to a wedding this weekend, but I think it is complete. Thank you very much for all your help and timely responses.

[FlameRetired]

You are welcome. Thank you for the feedback.

When you are ready ... if that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It is very helpful.

Enjoy the wedding!

[stuver]

Hey buddy, the wedding was great and thanks for the wishes. I've another question regarding conditional formatting. I am trying to compare two different columns with different data lengths and only highlight the cells if different in col b from col m (pretty much comparing a data set to a master list). In the attached example there is a conditional format I found from this website, but it highlights the same and not different values. I am also not sure how to make it check a certain set either (ie between b5:b22).

[FlameRetired]

stuver

This is a new question unrelated to the original. It warrants starting a new thread with a new title. Be sure to include your newest upload.

That will likely draw more participants as well.

I'll stay alert for your new thread.