+ Reply to Thread
Results 1 to 6 of 6

Formula to add with certain criteria

  1. #1
    Registered User
    Join Date
    01-08-2013
    Location
    Oxnard, Ca
    MS-Off Ver
    Excel 2007
    Posts
    6

    Formula to add with certain criteria

    I am trying to set up a workbook to help with scheduling on a production line. There are five stations that can run five different products, each product can have up to three different stages tied to one robot. I need a formula that will help with the tedious task of keeping track of quantities of various stages placed on the schedule. I am using 2010 at home and 2007 at work. For example:

    Cell

    A B C

    QTy PRODUCT STAGES
    1 1 12154 2
    2 1 3005 1
    3 1 3005 2
    4 3 12154 6
    5 2 3005 3
    6 6 6220 5
    7 6 6220 1
    8 6 6220 2
    9 10 12154 1
    10 10 12154 2


    How would a formula be written so that when a number is placed in column c the corresponding quantity from column is added and placed into the designated cell? For instance, if cell E6 is designated to receive the sum of all stage 2 quantities it would display 18 since there are four products requiring stage 2 ( 12154,3005,6220, and 12154 again) and the combined quantity is 18. Would this be a function of a formula or a macro or is it even possible?

    Thanks in advance

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula to add with certain criteria

    Maybe this formula entered in cell E6:

    =SUMIF(C:C,2,A:A)

    Or, if you need the specific ranges:

    =SUMIF(C2:C11,2,A2:A11)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Formula to add with certain criteria

    Maybe

    =SUMPRODUCT(--(C2:C11=2),--(A2:A11))

    Column C is for Stage Column and criteria is 2 (Stage 2) and Column A is for Qty Column, and the results for Stage 2 is 18.

    Regards
    Azumi

  4. #4
    Registered User
    Join Date
    01-08-2013
    Location
    Oxnard, Ca
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Formula to add with certain criteria

    Worked like a charm,

    Thanks

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula to add with certain criteria

    Quote Originally Posted by azumi View Post
    Maybe

    =SUMPRODUCT(--(C2:C11=2),--(A2:A11))
    No need for the double unary minus on column A.

    =SUMPRODUCT(--(C2:C11=2),A2:A11)

    However, SUMIF is more efficient.

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula to add with certain criteria

    You're welcome. Thanks for the feedback!

+ 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. [SOLVED] IF Formula returns #N/A even when criteria if criteria not met
    By Ellice16 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-11-2013, 10:51 PM
  2. [SOLVED] one formula value if critera A is met or a second formula value if criteria B is met
    By sland01 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-07-2013, 11:32 AM
  3. [SOLVED] Sumif Formula or Sumproduct Formula? Three Criteria.
    By oHUTCHYo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-28-2013, 10:50 AM
  4. SumIF formula with criteria is Text inside a Formula?
    By excellentlearner in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-25-2012, 06:40 PM
  5. Replies: 1
    Last Post: 05-16-2011, 05:00 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