Results 1 to 3 of 3

Thread: Adding Formula

  1. #1
    Acolyte Wings's Avatar
    Join Date
    Feb 2015
    Posts
    29
    Articles
    0
    Excel Version
    2013

    Red face Adding Formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Afternoon, really appreciate it if someone could help. trying to come up with a formula that will add cells and give one total for different refs. For instance we have a spread sheet with part numbers in column A which appear in various rows down the spreadsheet and then in a different column there is the quantities for those refs. the bit I'm struggling with is I only want to count the ones without the word legacy which in another column.

    Catalogue Ref Description Count Unit Price
    A055 Sofa 2
    A083 Bench Seat 5
    A019 Admin Desk 10 Legacy
    A019 Admin Desk 5
    A055 Sofa 5 Legacy


    So I would want to end up with only 5 for the ref A019 not 15 as there are 10 with the word legacy in the 4th column and only 2 of the ref A055 etc.

    Hopefully that makes sense. It's only a very simple version of my spread sheet.

    Look forward to someone's kind response.

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    You can use COUNTIFS() to count based on multiple criteria.

    e.g.

    Code:
    =COUNTIFS(A:A,"A019",D:D,"<>Legacy")
    will result in 5 because in only counts A019 in column A where column D does not contain "Legacy".

    You can replace the "A019" reference with cell reference containing the lookup value...


  3. #3
    Acolyte caabyyc's Avatar
    Join Date
    Dec 2014
    Location
    Burnaby/Calgary
    Posts
    26
    Articles
    0
    Excel Version
    2016
    For Total Count value
    Code:
    =SUMIF(D:d,"<>Legacy",C:C)          (=12)
    For each individual Ref
    Code:
    E2=IF(D2<>"Legacy",SUMIFS(C:C,D:d,"<>Legacy",A:A,A2),"/")
    Last edited by caabyyc; 2019-09-03 at 11:08 PM.

Posting Permissions

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