Difficult Countif with multiple sheets

[stakar]

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 ar
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' th

AI2 finds the start of the column and the AI3 the end of it. For th
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 th
'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 advanc

 

[Guest]

point to the range in each sheet, it will write the
proper code for the name of the sheet. Each range needs
to be separated by a comma.

the range will look something like this: M5:Z5,sheet K
A5:z5

 

[stakar]

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[/QUOTE]
Is any other idea???

Thanks

*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


---


.
* Please dont use the