If Worksheet Function Problem

E

Excel'ed_Failures

Currently I am working on a project where I am trying to extract data from
multiple tabs.

What I want to do:
I want my formula to search one group of cells on multiple tabs for the word
"Scrap" then if that is true then count in another set of cells the number of
times the word "BCU25" occurs.

My formula:
=IF('1:31'!C23:C41="Scrap",COUNTIF('1:31'!E23:E41,"BCU25"),"None")

My Problem:
This formula produces a #REF! error.

My Question:
Can the IF line of worksheet functions be used to target a range of multiple
cells on diffrent Tabs?
 
T

Trevor G

This is possible by using array formulas. For full details read your help,
but it involves entering your formula with Ctl-Shift-Enter. This will place
parenthesis around your formula (note these parenthesis are for Excel only
and CANNOT be just typed in) that let EXCEL know to treat the formula as an
array. This allows for nested if and sum/count routines to work together
with limitless variables. Normally u would have to use sumif or countif, but
these limit u to 1 set of variables.
 
F

Frank Kabel

Hi
Two possible approaches:

1. Harlan Grove showed a formula approach for a conditional sum/count
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('1:31'!C23:C41)="Scrap")*(THREED('1:31'!E23:E41)="BCU25"))
enter this as array formula (CTRL+SHIFT+ENTER). This will sum all
values from column C in which column A and column B contain your
criteria

--
Regards
Frank Kabel
Frankfurt, Germany

"Excel'ed_Failures" <Excel'[email protected]>
schrieb im Newsbeitrag
news:[email protected]...
 
Top