Adding under three criteria across many worksheets.

[isafloor]

I have the following formula constructed from different forums I checked out and it returns a #VALUE! error.

=SUMIFS(INDIRECT("'"&$X$21:$X$33&"'!$F:$F"),INDIRECT("'"&$X$21:$X$33&"'!$A:$A"),">"&R1,INDIRECT("'"&$X$21:$X$33&"'!$A:$A"),"<="&S1,INDIRECT("'"&$X$21:$X$33&"'!$B:$B"),"="&$A1)

Where:

X21:X33, contains a list of the names of the sheets I want to include in the result.
F:F, contains sales amounts
A:A, contains sales dates
R1, contains a the previous Saturday date, for example January 3 2015
S1, contains the next Saturday date, in this case January 10 2015
A1, is the name of a salesperson

So I have a sheet per product (Soap, Shampoo, etc.), and I want to add up the sales each sales rep has made by week regardless of "product" (hence the need to add across sheets using three criteria: sales rep, later than X date and before Y date).

What am I doing wrong? I need this for an upcoming presentation, help!

[bebo021999]

It appears correct.

Anyway, try to check column F in all sheets to make sure they contain number value (not text)

[isafloor]

There seems to be something wrong with the reference to the worksheets, because I substituted all the "INDIRECT" for the name of one particular worksheet and had a result thrown back. Don't understand what is going on.