Results 1 to 9 of 9

Thread: need help for formula
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2011
    Posts
    213
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default need help for formula

    Hello all i got 2 columns A and B , with random values in them.

    I need in column C to put all of the values ​​that appear in the column A with no repeat, and in column D to put the sum of all values in column B which correspond to the values ​​of a column A. I need that to be formula conection, not advanced filter.

    example

    A B C D
    1 3 1 4
    2 5 2 12
    3 6 3 8
    2 7 4 10
    1 1
    4 6
    3 2
    4 4

    Thank you this is very important to me, because i always have problem with advanced filter
    Last edited by chobanne; Nov 30th, 2013 at 07:37 PM.

  2. #2
    Board Regular
    Join Date
    Sep 2011
    Posts
    384
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: need help for formula

    Hi Chobanne,

    Why is the bottom value in column C "4"? should it not be blank because 2 is already present in the 2nd row of column A?

    I don't follow the logic in column D. Should D equal A plus B? It's that in the first row but not the other rows.

    Poiu

  3. #3
    Board Regular
    Join Date
    Jul 2011
    Posts
    213
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: need help for formula

    I dont know what you understand, but in C i want to show every value that exist in column A (thats 1,2,3 and 4) and in D i want to get sum of values in B which are next to this values in column A (thats for 1(3+1=4),2(5+7=12),3(6+2=8) and 4(6+4=10))

  4. #4
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    6,723
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: need help for formula

    In C2, copied down...
    =IFERROR(INDEX($A$2:$A$9,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$9),0,0),0)),"")

    Then in D2, copied down...
    =SUMIF($A$2:$A$9,E2,$B$2:$B$9)

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

  5. #5
    Board Regular
    Join Date
    Sep 2011
    Posts
    384
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: need help for formula

    OK I understand column C now, but still now column D - please could you retry?

    edit: ignore me - FDibbins beat me to it!

  6. #6
    Board Regular
    Join Date
    Jul 2011
    Posts
    213
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: need help for formula

    Quote Originally Posted by FDibbins View Post
    In C2, copied down...
    =IFERROR(INDEX($A$2:$A$9,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$9),0,0),0)),"")

    Then in D2, copied down...
    =SUMIF($A$2:$A$9,E2,$B$2:$B$9)
    first formula is good but second dont, whats E2?

  7. #7
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    6,723
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: need help for formula

    Apologies, I had thise 2 formulas in E and F, so that I vcould keep your original data to compare with.

    It should have read...
    =SUMIF($A$2:$A$9,C2,$B$2:$B$9)

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

  8. #8
    Board Regular
    Join Date
    Jan 2012
    Posts
    254
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: need help for formula

    Hello,

    Here is a possible solution. I found this amazing formula for unique values online at Create a unique distinct alphabetically sorted list, extracted from a column in excel | Get Digital Help - Microsoft Excel resource

    Note that the formula uses a few named ranges for simplicity, make sure you add those, If you want make them dynamic with this formulas:

    Code:
    List=OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1,1)
    Code:
    Values=OFFSET(Sheet2!$B$1,1,0,COUNTA(Sheet2!$B:$B)-1,1)
    Also the formula in C1:C9 is an array formula press ctr+Shift+enter to enter it.

    Here is the main formula for the unique values:
    Code:
    =IF(COUNT(IF(MATCH(List,List,0)=(ROW(List)-ROW(List_Start)+1),COUNTIF(List,"<"&List)+1,""))>(ROW(List)-ROW(List_Start)),INDEX(List,MATCH(SMALL(IF(MATCH(List,List,0)=(ROW(List)-ROW(List_Start)+1),COUNTIF(List,"<"&List)+1,""),(ROW(List)-ROW(List_Start)+1)),IF(MATCH(List,List,0)=(ROW(List)-ROW(List_Start)+1),COUNTIF(List,"<"&List)+1,""),0)),"")
    This is the formula for the calculations:
    Code:
    =IF(C2="","",SUMPRODUCT(Values,--(List=C2)))
    I have attached a copy of the work book in case you have questions https://dl.dropboxusercontent.com/u/.../Solution.xlsx

    Thanks

  9. #9
    Board Regular
    Join Date
    Jul 2011
    Posts
    213
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: need help for formula

    Thats perfect, works perffect. Thank you very much

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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