I've just spotted that I missed a bracket from the end of the SP
formula - should be this:

=SUMPRODUCT((INDIRECT("'"&A2&"'!E1:E1000")=$D$1)*( INDIRECT("'"&A2&"'!
G1:G1000")=$F$1)*(INDIRECT("'"&A2&"'!B1:B1000")))

Hope this helps.

Pete

On Jan 16, 5:30*pm, Pete_UK wrote:
So presumably you enter a word like "pending" in D1 on the summary
sheet, and you now want to enter a date in F1, and you want the sum of
column B (from the sheet stored in A2) where column E = summary!D1 and
column ? = summary!F1 ?

You can do this with SP, but you need to realise that you can't have
full-column references with SP (unless you are using XL 2007). This is
what it would look like:

=SUMPRODUCT((INDIRECT("'"&A2&"'!E1:E1000")=$D$1)*( INDIRECT("'"&A2&"'!
G1:G1000")=$F$1)*(INDIRECT("'"&A2&"'!B1:B1000"))

where I have assumed that your dates are in column G on those other
sheets, and that you may have up to 1000 rows in your data sheets.
I've also assumed that the formula is in the summary sheet, so you
don't need to specify the sheet name in front of D1, F1.

What I meant by using a helper column is that you could have a formula
like:

=E1&G1

in (say) Z1 of your data sheets, copied down as far as you need.

Then you could have:

=SUMIF(INDIRECT("'"&A2&"'!Z:Z"),$D$1&$F$1,INDIRECT ("'"&A2&"'!B:B"))

which will operate as quickly as your current formula. The SP version
may be significantly slower, depending on how many rows you operate it
over. Both should give the same results, however.

Hope this helps.

Pete