Sumif Across multiple worksheets

[Guest]

I work as a consultant to school districts and in doing so I set up
enrollment projection models in excel workbooks. I am in the process of
refining our models and reducing the work we do. My current workbook
consists of about 350 worksheets of data (they normally are not this huge).
I am trying to set up a summary sheet of all worksheets using a SUMIF
command. I want to sum enrollments in cell E93 based on the school name
entered in cell S16. My worksheets start with SAZ 1110823:SAZ 5172626 (based
on our label system).
I tired this command:

=SUMIF('SAZ 1110823:SAZ 5172626'!S16,"Agua Caliente",'SAZ 1110823:SAZ
5172626'!E93)

And it came back with an error of "A value used in the formula is of the
wrong data type". I'm not sure what step to take. I thought this would be a
pretty simple formula, but have been stuck for several hours, and haven't
really found a solution searching around the message board.

Thank you.

 

[Domenic]

Untested...

If you download and install the free add-in Morefunc.xll, you can use
the following formula...

=SUMPRODUCT(--(THREED('SAZ 1110823:SAZ 5172626'!S16)="Agua
Caliente"),THREED('SAZ 1110823:SAZ 5172626'!E93))

The add-in can be found in the following link...

http://xcell05.free.fr/english/index.html

Hope this helps!

 

[Guest]

Worked beautifully!

Thanks for your assistance.

Untested...

If you download and install the free add-in Morefunc.xll, you can use
the following formula...

=SUMPRODUCT(--(THREED('SAZ 1110823:SAZ 5172626'!S16)="Agua
Caliente"),THREED('SAZ 1110823:SAZ 5172626'!E93))

The add-in can be found in the following link...

http://xcell05.free.fr/english/index.html

Hope this helps!