Difficult Countif with multiple sheets

Hi!
Im using the following formula to get a countif
=COUNTIF(INDEX('Ê1'!4:4;1;$AI$2):INDEX('Ê1'!4:4;1; $AI$3);0)
which is filling down to the cell 1000

The AI2 cell is
{=MIN(IF('K1'!B2:IV2=S3;COLUMN('Ê1'!B2:IV2)))}

and AI3 cell is

{=MAX(IF('K1'!B2:IV2=S3;COLUMN('K1'!B2:IV2)))}

and the S3 cell is a list having '01,'02','03',04' etc. The values are
getting from the cells A1:I1 from the sheet 'K1'

I have a sheet called 'K1' and it is like

A B C D E F G H I
1 01 01 01 01 02 02 02 03 03
2 1 2 1 0 0 1 4 5 0
3 0 5 0 1 1 2 1 0 1
4 1 2 0 0 0 1 0 0 1

So each time i choose from the list (cell S3) a value eg. the '01' the

AI2 finds the start of the column and the AI3 the end of it. For the
example using the value '01' the AI2 will be 2 and the AI3 will be 5.
At last the countif uses the values of the AI2 and AI3 and counts the
'0' or the '1'or ....

What i want is
To use the above but with multiple sheets. So i have except the K1 ,
the K2 , K3 and K4.
Can anyone help me??

Thanks in advance


---
Message posted from http://www.ExcelForum.com/

Sorry , but i dont understand
The whole idea is how to have a countif with multiple criteria for
multiple sheets!

I want to count how many '1' are in A2:IV2 when the A1:IV1 is "A" but
for multiple sheets (sheet 1 to sheet4).

I have tried something but it didnt work. I also use the THREED() and
COUNTIF.3D functions but i didnt manage to make it works
SUMPRODUCT is extremely slow for what i have to do[/color]
Is any other idea???

Thanks



Anonymous wrote:[color=blue]
*point to the range in each sheet, it will write the=20
proper code for the name of the sheet. Each range needs=20
to be separated by a comma.

the range will look something like this: M5:Z5,sheet K=20
A5:z5
-----Original Message-----
Hi!
Im using the following formula to get a countif
=3DCOUNTIF(INDEX('=CA1'!4:4;1;$AI$2):INDEX('=CA1' !
4:4;1;$AI$3);0)
which is filling down to the cell 1000

The AI2 cell is=20
{=3DMIN(IF('K1'!B2:IV2=3DS3;COLUMN('=CA1'!B2:IV2) ))}

and AI3 cell is

{=3DMAX(IF('K1'!B2:IV2=3DS3;COLUMN('K1'!B2:IV2))) }

and the S3 cell is a list having '01,'02','03',04' etc.=20
The values are
getting from the cells A1:I1 from the sheet 'K1'

I have a sheet called 'K1' and it is like

A B C D E F G H I
1 01 01 01 01 02 02 02 03 03
2 1 2 1 0 0 1 4 5 0
3 0 5 0 1 1 2 1 0 1
4 1 2 0 0 0 1 0 0 1 =20

So each time i choose from the list (cell S3) a value=20
eg. the '01' the

AI2 finds the start of the column and the AI3 the end of=20
it. For the
example using the value '01' the AI2 will be 2 and the=20
AI3 will be 5.
At last the countif uses the values of the AI2 and AI3=20
and counts the
'0' or the '1'or ....

What i want is
To use the above but with multiple sheets. So i have=20
except the K1 ,
the K2 , K3 and K4.
Can anyone help me??

Thanks in advance


---
Message posted from http://www.ExcelForum.com/

.
* Please dont use the


---
Message posted from http://www.ExcelForum.com/