I want to sum a part code if a different cell reference is I for example:
I J P Q (headings)
33114606 current 30
33114606 current 30
33114606 August 40
33114606 QOH 40

I want the return value to be 40 for if I
Then another formula that returns 60 for P where current
Then another formula that returns 40 for P where August
I have multible part codes in the list I want to do this to.

I'm not sure I understand fully, but try the following...

Assumptions:

A1:F5 contains your table

C1:F1 contains your headers/labels I, J, P, and Q

Formula:

=SUMPRODUCT(--(A2:A5=33114606),--(B2:B5="current"),INDEX(C2:F5,0,MATCH("P
",C1:F1,0)))

OR

=SUMPRODUCT(--(A2:A5=H2),--(B2:B5=I2),INDEX(C2:F5,0,MATCH(J2,C1:F1,0)))

....where H2 contains your 'Part Code', I2 contains your status, such as
'Current', and J2 contains your column of interest, designated by a
heading such as 'P'. Adjust the range accordingly.

Hope this helps!


In article ,
"La Donna" <La wrote:

I want to sum a part code if a different cell reference is I for example:
I J P Q (headings)
33114606 current 30
33114606 current 30
33114606 August 40
33114606 QOH 40

I want the return value to be 40 for if I
Then another formula that returns 60 for P where current
Then another formula that returns 40 for P where August
I have multible part codes in the list I want to do this to.

Thank you. It worked perfect. I had never used SUMPRODUCT.

"Domenic" wrote:

I'm not sure I understand fully, but try the following...

Assumptions:

A1:F5 contains your table

C1:F1 contains your headers/labels I, J, P, and Q

Formula:

=SUMPRODUCT(--(A2:A5=33114606),--(B2:B5="current"),INDEX(C2:F5,0,MATCH("P
",C1:F1,0)))

OR

=SUMPRODUCT(--(A2:A5=H2),--(B2:B5=I2),INDEX(C2:F5,0,MATCH(J2,C1:F1,0)))

....where H2 contains your 'Part Code', I2 contains your status, such as
'Current', and J2 contains your column of interest, designated by a
heading such as 'P'. Adjust the range accordingly.

Hope this helps!


In article ,
"La Donna" <La wrote:

I want to sum a part code if a different cell reference is I for example:
I J P Q (headings)
33114606 current 30
33114606 current 30
33114606 August 40
33114606 QOH 40

I want the return value to be 40 for if I
Then another formula that returns 60 for P where current
Then another formula that returns 40 for P where August
I have multible part codes in the list I want to do this to.