JKwan
06-06-2012, 11:34 AM
I have this formula:
=SUMPRODUCT(--(PivotSource!$R$2:$R$642<>"N/A")*((PivotSource!$R$2:$R$642="High Level")+(PivotSource!$R$2:$R$642="Low Level")+(PivotSource!$R$2:$R$642="High Pressure")+(PivotSource!$R$2:$R$642="Low Pressure")+(PivotSource!$R$2:$R$642="High Temp")+(PivotSource!$R$2:$R$642="Low Temp")+(PivotSource!$R$2:$R$642="Misdirected Flow"))*(PivotSource!$AA$2:$AA$642=$A4))
Is there a way to shorten it? Basically, if I can compare using an array of items, I don't know if SumProduct can do that.
Thanks
=SUMPRODUCT(--(PivotSource!$R$2:$R$642<>"N/A")*((PivotSource!$R$2:$R$642="High Level")+(PivotSource!$R$2:$R$642="Low Level")+(PivotSource!$R$2:$R$642="High Pressure")+(PivotSource!$R$2:$R$642="Low Pressure")+(PivotSource!$R$2:$R$642="High Temp")+(PivotSource!$R$2:$R$642="Low Temp")+(PivotSource!$R$2:$R$642="Misdirected Flow"))*(PivotSource!$AA$2:$AA$642=$A4))
Is there a way to shorten it? Basically, if I can compare using an array of items, I don't know if SumProduct can do that.
Thanks