+ Reply to Thread
Results 1 to 27 of 27

Really need help with Sum = Going insane over this

  1. #1
    Registered User
    Join Date
    08-31-2020
    Location
    Sweden
    MS-Off Ver
    English
    Posts
    20

    Question Really need help with Sum = Going insane over this

    Hello. Im gonna cut straight to the chase, i found this forum after hours of searching and needing help with something that supposedly is an easy peasy fix for excel savy people out here.

    Problem: I need to count how many pairs from 2 cells appear.

    This is my list of numbers:

    B | C
    1005 2488
    1005 2488
    1005 2488
    1005 2488
    1005 2488
    1005 2488
    1005 2488
    1005 2488
    1005 2488
    1005 2488
    1005 2488
    1005 2488
    1005 2488
    1005 2488
    1005 2488
    1005 2489
    1005 2489
    1005 2489
    1005 2489
    1005 2489
    1005 2489
    1005 2491
    1005 2491
    1005 2491
    1005 188
    1005 188
    1005 190
    1005 190
    1005 190
    1005 190
    1016 1395
    1016 2488
    1016 2488
    1016 2488
    1016 2488
    1016 2488
    1016 2488
    1016 796




    I need this to turn into this:
    B C
    15 1005 2488
    6 1005 2489
    3 1005 2491
    2 1005 188
    4 1005 190
    1 1016 1395
    6 1016 2488
    1 1016 796

    HOW! please help me, any help would be greatly appreciated. I've written countless formulas but cant seem to find the answer to this simple problem xD

  2. #2
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,775

    Re: Really need help with Sum = Going insane over this

    Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

    What version of office do you have ?

  3. #3
    Registered User
    Join Date
    08-31-2020
    Location
    Sweden
    MS-Off Ver
    English
    Posts
    20

    Re: Really need help with Sum = Going insane over this

    Quote Originally Posted by CARACALLA View Post
    Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

    What version of office do you have ?
    Version: 18.2006.1031.0
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,775

    Re: Really need help with Sum = Going insane over this

    H2=IFERROR(INDEX(B$2:B$100,SMALL(IF(FREQUENCY(IF(B$2:B$100<>"",MATCH($B$2:$B$100&$C$2:$C$100,$B$2:$B$100&$C$2:$C$100,0)),ROW(B$2:B$100)-ROW(B$2)+1),ROW(B$2:B$100)-ROW(B$2)+1),ROWS($B$2:B2))),"")

    Control+shift+enter

    copy across and down



    K2=COUNTIFS($B$2:$B$100,$H2,$C$2:$C$100,$I2)

    Copy down
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-31-2020
    Location
    Sweden
    MS-Off Ver
    English
    Posts
    20

    Re: Really need help with Sum = Going insane over this

    Looks like it's working. But what do you mean copy across and down? Do i need to do this for every single line? Or does this work with any numbers as long as i have your formula code in there?

  6. #6
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,775

    Re: Really need help with Sum = Going insane over this

    formula=copy to the right and down

  7. #7
    Registered User
    Join Date
    08-31-2020
    Location
    Sweden
    MS-Off Ver
    English
    Posts
    20

    Re: Really need help with Sum = Going insane over this

    It does not appear to work when you add new numbers in the list, i added a few new numbers here and it does not show the amount. I added 1005 2488 at the bottom which added up in your formula but when i added 1005 2489 it did not add up. What am i missing?

    Also when i tryed changing a number above it took away from all the other amounts in the formula list, i think there is something wrong with the formula dude
    Attached Files Attached Files
    Last edited by domenix; 08-31-2020 at 03:21 PM.

  8. #8
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,775

    Re: Really need help with Sum = Going insane over this

    attach your file.

  9. #9
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,775

    Re: Really need help with Sum = Going insane over this

    why don't work ?
    Attached Files Attached Files

  10. #10
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Really need help with Sum = Going insane over this

    I used Concatenate like so copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    And then created a pivot table from Column C using Count of Column C
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  11. #11
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Really need help with Sum = Going insane over this

    Alternative option with non-array formula:
    M2:
    Please Login or Register  to view this content.
    Copy down and accross to column N

    Attach here with both solutions for you.
    Attached Files Attached Files
    Quang PT

  12. #12
    Registered User
    Join Date
    08-31-2020
    Location
    Sweden
    MS-Off Ver
    English
    Posts
    20

    Re: Really need help with Sum = Going insane over this

    Quote Originally Posted by ChemistB View Post
    I used Concatenate like so copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    And then created a pivot table from Column C using Count of Column C
    This doesnt work either, it needs to be in cells so i can easily copy past it as seen in my post with values and values manually counted above. Also when i change values in the cells on the left in your excel dokument it does not add up in the pivot table and also messes up actual amounts.

  13. #13
    Registered User
    Join Date
    08-31-2020
    Location
    Sweden
    MS-Off Ver
    English
    Posts
    20

    Re: Really need help with Sum = Going insane over this

    Quote Originally Posted by bebo021999 View Post
    Alternative option with non-array formula:
    M2:
    Please Login or Register  to view this content.
    Copy down and accross to column N

    Attach here with both solutions for you.
    Looks like your formula is the only one that works so far.I will test it out later and reply if it works and if the issue is solved !

    Thank you everyone for all the help in trying to figure out this formula for me !
    I also need to mention that the amount of lines each configure files with numbers have can reach up to 40.000+ configurations, which takes a few days to manually check, which is why i really need this formula

    Mvh Robin

  14. #14
    Registered User
    Join Date
    08-31-2020
    Location
    Sweden
    MS-Off Ver
    English
    Posts
    20

    Re: Really need help with Sum = Going insane over this

    I tested this with one of our project configurations. It seems to not add up a few of the amounts, maybe i just need to copy your formula to more down?

    edit: i copied your formula down but there might be an issue cause the amount comes out to zero on most of the count ups.
    Attached Files Attached Files
    Last edited by domenix; 09-01-2020 at 06:36 AM.

  15. #15
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Really need help with Sum = Going insane over this

    Not sure what you mean by the results need to be in cells? When you make changes to your data, the Pivot table needs to be "Refreshed" (right click menu). I added a table where the pivot table contents are pulled out and separated.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    08-31-2020
    Location
    Sweden
    MS-Off Ver
    English
    Posts
    20
    Quote Originally Posted by ChemistB View Post
    Not sure what you mean by the results need to be in cells? When you make changes to your data, the Pivot table needs to be "Refreshed" (right click menu). I added a table where the pivot table contents are pulled out and separated.
    Well i need to be able to copy the results straight to another software to be scanned. And needing to refresh and stuff just seems a bit messy and could mess up alot if i forget. Bebo’s simple calculation list is perfect and just what i need, just need to get it to work for more than 100 lines, but i did ask him so he probably knows how in a bit.

  17. #17
    Registered User
    Join Date
    08-31-2020
    Location
    Sweden
    MS-Off Ver
    English
    Posts
    20

    Re: Really need help with Sum = Going insane over this

    ok Chemist that actually works with the extra colum you put next to the pivot table. However when i tryed to add just a random number of 1000 lines it barely counts any of them, how do i fix so that this actually works for infinite amount of lines? See file
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    08-31-2020
    Location
    Sweden
    MS-Off Ver
    English
    Posts
    20

    Re: Really need help with Sum = Going insane over this

    Quote Originally Posted by ChemistB View Post
    Not sure what you mean by the results need to be in cells? When you make changes to your data, the Pivot table needs to be "Refreshed" (right click menu). I added a table where the pivot table contents are pulled out and separated.
    Post Above

  19. #19
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Really need help with Sum = Going insane over this

    In sample, for 400 rows only, try to extend it to,i.e, 1000 rows the it should works.

    =IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$400)/(1-COUNTIFS($M$1:$M1,$B$2:$B$400,$N$1:$N1,$C$2:$C$400)),1)),"")

    100 to 1000:
    =COUNTIFS($B$2:$B$100,$M2,$C$2:$C$100,$N2)

  20. #20
    Registered User
    Join Date
    08-31-2020
    Location
    Sweden
    MS-Off Ver
    English
    Posts
    20

    Re: Really need help with Sum = Going insane over this

    I did try that but same errors with zeros

  21. #21
    Registered User
    Join Date
    08-31-2020
    Location
    Sweden
    MS-Off Ver
    English
    Posts
    20

    Re: Really need help with Sum = Going insane over this

    Quote Originally Posted by bebo021999 View Post
    In sample, for 400 rows only, try to extend it to,i.e, 1000 rows the it should works.

    =IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$400)/(1-COUNTIFS($M$1:$M1,$B$2:$B$400,$N$1:$N1,$C$2:$C$400)),1)),"")

    100 to 1000:
    =COUNTIFS($B$2:$B$100,$M2,$C$2:$C$100,$N2)
    It seems to actually work now, it did count double for some reason but im looking into it. Now all i need is some way to copy the formula created numbers. Because when copying it copies the formula in the cells instead of the numbers. Maybe some sort of get numbers like chemist made in his?

  22. #22
    Registered User
    Join Date
    08-31-2020
    Location
    Sweden
    MS-Off Ver
    English
    Posts
    20

    Re: Really need help with Sum = Going insane over this

    Quote Originally Posted by bebo021999 View Post
    In sample, for 400 rows only, try to extend it to,i.e, 1000 rows the it should works.

    =IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$400)/(1-COUNTIFS($M$1:$M1,$B$2:$B$400,$N$1:$N1,$C$2:$C$400)),1)),"")

    100 to 1000:
    =COUNTIFS($B$2:$B$100,$M2,$C$2:$C$100,$N2)
    Here it is finished and seems to be working, now all i need as mentioned above some way of grabbing the numbers without the formula to for example like in this dokument.
    Attached Files Attached Files

  23. #23
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Really need help with Sum = Going insane over this

    See attachment
    Attached Files Attached Files

  24. #24
    Registered User
    Join Date
    08-31-2020
    Location
    Sweden
    MS-Off Ver
    English
    Posts
    20

    Re: Really need help with Sum = Going insane over this

    That does not work, there is still formulas in the cell to the right so i cant copy the "text" aspect of the numbers.

  25. #25
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Really need help with Sum = Going insane over this

    The only way to copy without formulas is to
    1. Copy and "Paste values"
    or
    2. Use a macro to do the same thing.
    Do you want this to automatically paste the values into your table? If so, when? Each time you add a new value into columns A and B?

  26. #26
    Registered User
    Join Date
    08-31-2020
    Location
    Sweden
    MS-Off Ver
    English
    Posts
    20

    Re: Really need help with Sum = Going insane over this

    Quote Originally Posted by ChemistB View Post
    The only way to copy without formulas is to
    1. Copy and "Paste values"
    or
    2. Use a macro to do the same thing.
    Do you want this to automatically paste the values into your table? If so, when? Each time you add a new value into columns A and B?
    Everything that the formula calculates (i.e 15 600 700) need to be copied as text. So maybe i can try your solution which said paste values

  27. #27
    Registered User
    Join Date
    10-22-2020
    Location
    India
    MS-Off Ver
    2019
    Posts
    2

    Guide me for pivot table

    Hello guys, I am new to pivot table. I use ms office 2019. Kindly help me with the attached excel. I will be deeply grateful and also would be able to save my job.

    regards
    Tapan
    Attached Files Attached Files

+ 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. These are insane!!
    By FDibbins in forum The Water Cooler
    Replies: 18
    Last Post: 08-29-2020, 02:37 AM
  2. This is driving me insane... please help.
    By ajoh00n in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-17-2015, 10:43 AM
  3. [SOLVED] Sum row until criteria is met (driving me insane!)
    By Mbarnes in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-18-2012, 10:10 AM
  4. somebody help! iam going insane
    By hvisa in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-01-2007, 09:30 AM
  5. Going insane!
    By kik in forum Excel - New Users/Basics
    Replies: 10
    Last Post: 01-31-2007, 08:21 AM
  6. PivotCharts, PivotTables, I'm going insane
    By CBA88 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 01-04-2006, 09:15 PM
  7. I am going insane trying to work this out...
    By Paul Cooke in forum Excel General
    Replies: 5
    Last Post: 12-08-2005, 01:50 PM

Tags for this Thread

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