Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jan 2014
    Posts
    30
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Countif & Sumproduct

    Hi,

    In my sheet in column B (sorted in ascending order) i have duplicates, and if duplicates are found then sum column D and output the total result in E.

    e.g.

    Column 'B' Column 'D' Column 'E'
    123------------ 100
    123------------ 100
    123------------ 100 .........300
    145------------ 500
    153-------------800
    147-------------600
    147-------------600 ........1200
    478-------------200
    144-------------380
    144-------------750
    144-------------900
    144-------------330 ........2360

    Thanks in advance.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi John

    If your first entry for column B is in row 1, then enter this formula in cell [E1] and copy-down as required:
    Code:
    =IF(COUNTIF(B:B,B1)=1,"",IF(B2=B1,"",SUMIF(B:B,B1,D:D)))
    zeddy

  3. The Following User Says Thank You to zeddy For This Useful Post:

    John_2014 (2016-11-16)

  4. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    This will also work:
    Code:
    =IF(COUNTIF(B:B,B1)=1,"",IF(B2=B1,"",SUMPRODUCT((D:D)*(B:B=B1))))
    or
    =IF(AND(COUNTIF(B:B,B1)>1,COUNTIF($B$1:$B1,B1)=COUNTIF(B:B,B1)),SUMPRODUCT((D:D)*(B:B=B1)),"")
    Place in E1 then copy down
    Last edited by Maudibe; 2016-11-17 at 15:17.

Tags for this Thread

Posting Permissions

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