summing multiple worksheets with multiple criteria

Assuming that pipe!A21 contains a text value, try the following...

=SUMPRODUCT(--(T(INDIRECT(ROW(INDIRECT("1:18"))&"!D13"))=pipe!A21),--(T(INDIRECT(ROW(INDIRECT("1:18"))&"!B13"))="sold"),N(INDIRECT(ROW(INDIRECT("1:18"))&"!C13")))

If pipe!A21 contains a numeric value, try the following instead...

=SUMPRODUCT(--(N(INDIRECT(ROW(INDIRECT("1:18"))&"!D13"))=pipe!A21),--(T(INDIRECT(ROW(INDIRECT("1:18"))&"!B13"))="sold"),N(INDIRECT(ROW(INDIRECT("1:18"))&"!C13")))

Hope this helps!

Originally Posted by spirodem

i am trying to put together a formula that will sum all the values in a cell across 18 worksheets, based on 2 criteria. the current formula is {if(sum(('1'!d13=pipe!a21)*('1'!b13="sold")),'1'!c13,0)} - this formula works for one worksheet. when i try this {if(sum(('1:18'!d13=pipe!a21)*('1:18'!b13="sold")),sum('1:18'!c13,0)} i get an ref error.
any advice or is there another formula that i should use