+ Reply to Thread
Results 1 to 15 of 15

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

  1. #1
    Registered User
    Join Date
    01-12-2017
    Location
    india
    MS-Off Ver
    ms office 2013
    Posts
    21

    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
    Attached Files Attached Files

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    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.
    Attached Files Attached Files

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    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. #4
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    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)
    Teach me Excel VBA

  5. #5
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    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
    Last edited by ImranBhatti; 01-12-2017 at 12:37 PM.

  6. #6
    Registered User
    Join Date
    01-12-2017
    Location
    india
    MS-Off Ver
    ms office 2013
    Posts
    21

    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. #7
    Registered User
    Join Date
    01-12-2017
    Location
    india
    MS-Off Ver
    ms office 2013
    Posts
    21

    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. #8
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    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. #9
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    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
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    01-12-2017
    Location
    india
    MS-Off Ver
    ms office 2013
    Posts
    21

    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. #11
    Registered User
    Join Date
    01-12-2017
    Location
    india
    MS-Off Ver
    ms office 2013
    Posts
    21

    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. #12
    Registered User
    Join Date
    01-12-2017
    Location
    india
    MS-Off Ver
    ms office 2013
    Posts
    21

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

    Quote Originally Posted by Pete_UK View Post
    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. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

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

    Thanks for the kind comments.

    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. #14
    Registered User
    Join Date
    01-12-2017
    Location
    india
    MS-Off Ver
    ms office 2013
    Posts
    21

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

    Quote Originally Posted by Pete_UK View Post
    Thanks for the kind comments.

    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. #15
    Registered User
    Join Date
    01-12-2017
    Location
    india
    MS-Off Ver
    ms office 2013
    Posts
    21

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

    Quote Originally Posted by Pete_UK View Post
    Thanks for the kind comments.

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 7
    Last Post: 06-09-2016, 09:48 PM
  2. sort by group and total by each group
    By mheinemann in forum Excel General
    Replies: 3
    Last Post: 04-30-2015, 11:48 AM
  3. Replies: 1
    Last Post: 04-18-2014, 05:54 PM
  4. Replies: 1
    Last Post: 02-10-2014, 10:26 PM
  5. Replies: 8
    Last Post: 01-09-2014, 08:01 PM
  6. Replies: 2
    Last Post: 01-18-2013, 12:45 PM
  7. Sort a group of names based on the group total
    By ron2k_1 in forum Excel General
    Replies: 3
    Last Post: 08-13-2010, 01:16 PM

Bookmarks

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