COUNT ACROSS SEVERAL PAGES

=COUNTIF(A:Z!B335:B343,B22)

When using this to count between several sheets I return an error, but cant
work out where I am going wrong. any help would be great

Pete

--
(][ THIS EMAIL HAS BEEN SCANNED BY NORTON ANTIVIRUS ][)

One way

=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!B335:B343"),B22))


where MySheets is a named range with a list of all worksheet names
(insert>name>define) or you can use the range reference itself like H1:H26
(if you use the 26 sheet names), note that each sheet name that you want
included has to be there

--
Regards,

Peo Sjoblom

(No private emails please)


"OZDOC1050" <[email protected]> wrote in message
news:%[email protected]...
> =COUNTIF(A:Z!B335:B343,B22)
>
> When using this to count between several sheets I return an error, but
> cant work out where I am going wrong. any help would be great
>
> Pete
>
> --
> (][ THIS EMAIL HAS BEEN SCANNED BY NORTON ANTIVIRUS ][)
>

Thanks Peo,
but I cant get that to work ? im probably doing
something wrong ?

I will explain a little more if I can

=SUM(A:Z!E336)

I am building an order form and have a macro that inserts new sheets (
between sheets a and z ) these then need to total in the sub master order
form and the number of sheets it needs to total is a variable,

I could use the above option to sum all of these but have multiple and
variable second layer sub order forms which will total to a master, so in
between a and z I may have 3 reps each with multiple order forms that relate
to them.

each page has in cell c7 the owner of the sub order form ( name of the sub
master order form ) so I need the above sum to work but also reference cell
c7 to see if it matches and if so add to the sum.

thanks for any help you can give

Pete
--
(][ THIS EMAIL HAS BEEN SCANNED BY NORTON ANTIVIRUS ][)
"Peo Sjoblom" <[email protected]> wrote in message
news:[email protected]...
> One way
>
> =SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!B335:B343"),B22))
>
>
> where MySheets is a named range with a list of all worksheet names
> (insert>name>define) or you can use the range reference itself like H1:H26
> (if you use the 26 sheet names), note that each sheet name that you want
> included has to be there
>
> --
> Regards,
>
> Peo Sjoblom
>
> (No private emails please)
>
>
> "OZDOC1050" <[email protected]> wrote in message
> news:%[email protected]...
>> =COUNTIF(A:Z!B335:B343,B22)
>>
>> When using this to count between several sheets I return an error, but
>> cant work out where I am going wrong. any help would be great
>>
>> Pete
>>
>> --
>> (][ THIS EMAIL HAS BEEN SCANNED BY NORTON ANTIVIRUS ][)
>>
>

Hi,

Try this one:

=SUMPRODUCT(COUNTIF(INDIRECT(CHAR(ROW(INDIRECT("65:90")))&"!B335:B343"),B22))

Regards,
KL


"OZDOC1050" <[email protected]> wrote in message
news:%[email protected]...
> =COUNTIF(A:Z!B335:B343,B22)
>
> When using this to count between several sheets I return an error, but
> cant work out where I am going wrong. any help would be great
>
> Pete
>
> --
> (][ THIS EMAIL HAS BEEN SCANNED BY NORTON ANTIVIRUS ][)
>