# forming group and finding total for each group and select all group scoring above benchmar

1. ## forming group and finding total for each group and select all group scoring above benchmar

In a excel sheet there are 3 columns showing data of amount deposited by account holder to their account over a period of time
column A: Account number
Column B:name of account holder
Column C:amount deposited on a specific date by the account holder

Note: Column A and column B may consist of duplicate accounts as a account holder may deposit multiple times over a period of time

I have to find a way to find out all the accounts which deposited 1000 \$ or more than 1000 \$ to their account over a period of time either through single deposit or through multiple deposits.

Sample data sheet attached

2. ## Re: forming group and finding total for each group and select all group scoring above benc

You can use Subtotal Outline tool found in Data tab.

First sort data based on Account Number. And select entire range.

Subtotal at each change in account number. Use SUM function and add subtotal to Amount deposited.

You can also do this via Pivot Table as well.

See attached sample.

3. ## Re: forming group and finding total for each group and select all group scoring above benc

Put this formula in cell D2:

=IF(AND(SUMIF(\$A\$2:\$A\$19,A2,\$C\$2:\$C\$19)>=1000,COUNTIF(A\$2:A2,A2)=1),MAX(D\$1:D1)+1,"-")

then copy down to the bottom of your data.

Then you can use this formula in cell F2 (say):

=IFERROR(INDEX(A:A,MATCH(ROWS(\$1:1),D:D,0)),"")

and copy down until you start to get blanks to give you a list of the account numbers with deposits of 1000 \$ or above. If you want to know the actual deposits for those accounts, you can put this formula in G2:

=SUMIF(\$A\$2:\$A\$19,F2,\$C\$2:\$C\$19)

and copy this down.

Hope this helps.

Pete

4. ## Re: forming group and finding total for each group and select all group scoring above benc

Use this in the conditional formating formula option

=SUMIFS(\$C\$2:\$C\$19,\$A\$2:\$A\$19,\$A2)

5. ## Re: forming group and finding total for each group and select all group scoring above benc

Use this in the conditional formating formula option

=SUMIFS(\$C\$2:\$C\$19,\$A\$2:\$A\$19,\$A2)>=1000

6. ## Re: forming group and finding total for each group and select all group scoring above benc

For a large group of data it will be messy if a subgroup is created for each entry. I just want to find account numbers which deposited amount above a specific amount through single deposit or multiple deposit .Can u please guide me a little about using pivot table.Thanks

7. ## Re: forming group and finding total for each group and select all group scoring above benc

Thanks guys. I forgot to save the actual excel file to my lappy. Tomorrow at office I'll check each option and will let you know. Thanks again guys for your help.

8. ## Re: forming group and finding total for each group and select all group scoring above benc

Go to Insert ribbon tab->PivotTable

Select your data range (ex. Sheet1!\$A\$1:\$C\$19) and hit OK.

In PivotTable fields pane, move account number to Rows, and Amount deposted to Values (set it to Sum).
Attachment 496861

Right click on any of Row labels item in the PivotTable and select Value Filter.
Attachment 496863

Set Value Filter for "Sum of Amount deposted" greater than or equal to 1000.
Attachment 496864

9. ## Re: forming group and finding total for each group and select all group scoring above benc

Hi,

I've attached a Pivot Table - is this what you are after?

Regards

peterrc

10. ## Re: forming group and finding total for each group and select all group scoring above benc

Hey thanks mate. Your proposed solution works like a charm on the sample data. Can I ask for a little more ? with one more column added containing date of deposit(say column H)
Can I be able to enlist all such account(accounts which deposited amount more than or equal to specific amount) as a separate list containing account number, name, amount deposited and deposit date .
Thanks a ton again

11. ## Re: forming group and finding total for each group and select all group scoring above benc

Hey thanks. That looks great .I will like to know how you did that?

12. ## Re: forming group and finding total for each group and select all group scoring above benc

Originally Posted by Pete_UK
Put this formula in cell D2:

=IF(AND(SUMIF(\$A\$2:\$A\$19,A2,\$C\$2:\$C\$19)>=1000,COUNTIF(A\$2:A2,A2)=1),MAX(D\$1:D1)+1,"-")

then copy down to the bottom of your data.

Then you can use this formula in cell F2 (say):

=IFERROR(INDEX(A:A,MATCH(ROWS(\$1:1),D:D,0)),"")

and copy down until you start to get blanks to give you a list of the account numbers with deposits of 1000 \$ or above. If you want to know the actual deposits for those accounts, you can put this formula in G2:

=SUMIF(\$A\$2:\$A\$19,F2,\$C\$2:\$C\$19)

and copy this down.

Hope this helps.

Pete
great you are a genius

13. ## Re: forming group and finding total for each group and select all group scoring above benc

You now have a number of different (successful) approaches, so if that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

14. ## Re: forming group and finding total for each group and select all group scoring above benc

Originally Posted by Pete_UK

You now have a number of different (successful) approaches, so if that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete
You guys are great. I was very worried earlier today thinking about how am I going to deal with that big excel file in my office tomorrow. But now I am relaxed.Thanks

15. ## Re: forming group and finding total for each group and select all group scoring above benc

Originally Posted by Pete_UK

You now have a number of different (successful) approaches, so if that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete
You guys are great. I was very worried earlier today thinking about how am I going to deal with that big excel file in my office tomorrow. But now I am relaxed.Thanks

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1