Hello,

I have serval spreadsheets in one book. I need to
calculate a total for a particular cell on each
spreadsheet if a different cell on the same spreadsheet
meets certain criteria.

For example, I want to take the Price cell (D5, for
example) for each salesperson on all spreadsheets where
the Manager Name of the salesperson is "Bob" (cell A2 for
example) and place that total number in the cell of a new
spreasheet within the same book. So I want to total all
of the D5 cells on each spreadsheet where A2 = Bob and
link that total to the F5 cell of a new spreadsheet. The
tabs in the book are salespeople's names listed
alphabetically.

I hope how I've explained this makes sense!

Thanks for your help!!!
Di

Hi
Hi
1. Harlan Grove showed a formula approach for a conditional sum accross
multiple worksheets. Have a look at this thread
http://tinyurl.com/2manj

2. You may also try to download the free add-in Morefunc.xll
(http://longre.free.fr/english). The function THREED converts a 3D
array to a 2D array. This may work (the help of this function at least
suggested it). Note: I haven't tried this in much detail - so no
guarantee it will work. e.g. you may use the following formula

=SUM((THREED('sheet2:sheet50'!A1:A999)=2575)*(THRE ED('sheet2:sheet50'!B
1:B999)))
enter this as array formula (CTRL+SHIFT+ENTER). This will sum all
values from column B in which column A contains the value 2575

--
Regards
Frank Kabel
Frankfurt, Germany

"Di" schrieb im Newsbeitrag
...
Hello,

I have serval spreadsheets in one book. I need to
calculate a total for a particular cell on each
spreadsheet if a different cell on the same spreadsheet
meets certain criteria.

For example, I want to take the Price cell (D5, for
example) for each salesperson on all spreadsheets where
the Manager Name of the salesperson is "Bob" (cell A2 for
example) and place that total number in the cell of a new
spreasheet within the same book. So I want to total all
of the D5 cells on each spreadsheet where A2 = Bob and
link that total to the F5 cell of a new spreadsheet. The
tabs in the book are salespeople's names listed
alphabetically.

I hope how I've explained this makes sense!

Thanks for your help!!!
Di

Thanks Frank!!!

-----Original Message-----
Hi
Hi
1. Harlan Grove showed a formula approach for a
conditional sum accross
multiple worksheets. Have a look at this thread
http://tinyurl.com/2manj

2. You may also try to download the free add-in
Morefunc.xll
(http://longre.free.fr/english). The function THREED
converts a 3D
array to a 2D array. This may work (the help of this
function at least
suggested it). Note: I haven't tried this in much detail -
so no
guarantee it will work. e.g. you may use the following
formula

=SUM((THREED('sheet2:sheet50'!A1:A999)=2575)*(THR EED
('sheet2:sheet50'!B
1:B999)))
enter this as array formula (CTRL+SHIFT+ENTER). This will
sum all
values from column B in which column A contains the value
2575

--
Regards
Frank Kabel
Frankfurt, Germany

"Di" schrieb im
Newsbeitrag
...
Hello,

I have serval spreadsheets in one book. I need to
calculate a total for a particular cell on each
spreadsheet if a different cell on the same spreadsheet
meets certain criteria.

For example, I want to take the Price cell (D5, for
example) for each salesperson on all spreadsheets where
the Manager Name of the salesperson is "Bob" (cell A2
for
example) and place that total number in the cell of a
new
spreasheet within the same book. So I want to total all
of the D5 cells on each spreadsheet where A2 = Bob and
link that total to the F5 cell of a new spreadsheet.
The
tabs in the book are salespeople's names listed
alphabetically.

I hope how I've explained this makes sense!

Thanks for your help!!!
Di

.