# Formula to add with certain criteria

1. ## 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?

2. ## 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)

3. ## 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. ## Re: Formula to add with certain criteria

Worked like a charm,

Thanks

5. ## Re: Formula to add with certain criteria

Originally Posted by azumi
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. ## Re: Formula to add with certain criteria

You're welcome. Thanks for the feedback!

There are currently 1 users browsing this thread. (0 members and 1 guests)