Sumif Across multiple worksheets

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.

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!

In article <[email protected]>,
Giantrobot <[email protected]> wrote:

> 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.

Worked beautifully!

Thanks for your assistance.

"Domenic" wrote:

> 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!
>
> In article <[email protected]>,
> Giantrobot <[email protected]> wrote:
>
> > 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.
>