Sum if statements with conditions
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. |
All times are GMT +1. The time now is 04:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com