How can I turn a formula like:

=COUNTIF('Person 1'!$C8,"Off")+COUNTIF('Person 2'!$C8,"Off")+COUNTIF('Person
3'!$C8,"Off")+COUNTIF('Person 4'!$C8,"Off")+COUNTIF('Person 5'!$C8,"Off")

Into (something like):

=COUNTIF('Person 1':'Person 5'!$C8,"Off") ... this gives #NAME?
=COUNTIF('Person 1:Person 5'!$C8,"Off") ... this gives #VALUE!

It doesn't have to be the COUNTIF function so long as it achieves the same
ends.

I need to be able to add more people without a vast amount of repetition.
It would look neater and simpler too!

Thanks in advance

Trevor

You can change the SUMIF()'s in the solution provided he

http://www.mcgimpsey.com/excel/threedsumif.html

to COUNTIF()s, and use SUM() to sum across sheets. If you use wrapper
sheets (e.g., all your new sheets go between the first and last), you
can use

=SUM(first:last!Z1)

to include all intermediate sheets.

In article ,
"Trevor Shuttleworth" wrote:

How can I turn a formula like:

=COUNTIF('Person 1'!$C8,"Off")+COUNTIF('Person 2'!$C8,"Off")+COUNTIF('Person
3'!$C8,"Off")+COUNTIF('Person 4'!$C8,"Off")+COUNTIF('Person 5'!$C8,"Off")

Into (something like):

=COUNTIF('Person 1':'Person 5'!$C8,"Off") ... this gives #NAME?
=COUNTIF('Person 1:Person 5'!$C8,"Off") ... this gives #VALUE!

It doesn't have to be the COUNTIF function so long as it achieves the same
ends.

I need to be able to add more people without a vast amount of repetition.
It would look neater and simpler too!

Thanks in advance

Trevor

If you have Longre's free morefunc.xll add-in:

=COUNTIF.3D('Person 1':'Person 5'!$C8,"Off")

Otherwise:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetList&"'!C8") ,"Off"))

where SheetList refers to a range housing the relevant sheetnames in
separate cells.

Trevor Shuttleworth wrote:
How can I turn a formula like:

=COUNTIF('Person 1'!$C8,"Off")+COUNTIF('Person 2'!$C8,"Off")+COUNTIF('Person
3'!$C8,"Off")+COUNTIF('Person 4'!$C8,"Off")+COUNTIF('Person 5'!$C8,"Off")

Into (something like):

=COUNTIF('Person 1':'Person 5'!$C8,"Off") ... this gives #NAME?
=COUNTIF('Person 1:Person 5'!$C8,"Off") ... this gives #VALUE!

It doesn't have to be the COUNTIF function so long as it achieves the same
ends.

I need to be able to add more people without a vast amount of repetition.
It would look neater and simpler too!

Thanks in advance

Trevor

Many thanks.

I'm favouring the SUMPRODUCT option ... but at least now I have some options
to consider which is a huge plus

Regards

Trevor


"JE McGimpsey" wrote in message
...
You can change the SUMIF()'s in the solution provided he

http://www.mcgimpsey.com/excel/threedsumif.html

to COUNTIF()s, and use SUM() to sum across sheets. If you use wrapper
sheets (e.g., all your new sheets go between the first and last), you
can use

=SUM(first:last!Z1)

to include all intermediate sheets.

In article ,
"Trevor Shuttleworth" wrote:

How can I turn a formula like:

=COUNTIF('Person 1'!$C8,"Off")+COUNTIF('Person
2'!$C8,"Off")+COUNTIF('Person
3'!$C8,"Off")+COUNTIF('Person 4'!$C8,"Off")+COUNTIF('Person 5'!$C8,"Off")

Into (something like):

=COUNTIF('Person 1':'Person 5'!$C8,"Off") ... this gives #NAME?
=COUNTIF('Person 1:Person 5'!$C8,"Off") ... this gives #VALUE!

It doesn't have to be the COUNTIF function so long as it achieves the
same
ends.

I need to be able to add more people without a vast amount of repetition.
It would look neater and simpler too!

Thanks in advance

Trevor

Assuming that the sheets are not called person1 but maybe a persons name you
need to put all sheet names in question in a range like in

person1
person2
person3
etc..

now assume you have 5 different sheet names and put them in H1:H5

then you can use

=SUMPRODUCT(COUNTIF(INDIRECT("'"&H1:H5&"'!C8"),"OF F"))


if the sheets have the same name plus a number you can use

=SUMPRODUCT(COUNTIF(INDIRECT("'Person"&ROW(INDIREC T("1:5"))&"'!C8"),"OFF"))

--
Regards,

Peo Sjoblom

(No private emails please)


"Trevor Shuttleworth" wrote in message
...
How can I turn a formula like:

=COUNTIF('Person 1'!$C8,"Off")+COUNTIF('Person
2'!$C8,"Off")+COUNTIF('Person 3'!$C8,"Off")+COUNTIF('Person
4'!$C8,"Off")+COUNTIF('Person 5'!$C8,"Off")

Into (something like):

=COUNTIF('Person 1':'Person 5'!$C8,"Off") ... this gives #NAME?
=COUNTIF('Person 1:Person 5'!$C8,"Off") ... this gives #VALUE!

It doesn't have to be the COUNTIF function so long as it achieves the same
ends.

I need to be able to add more people without a vast amount of repetition.
It would look neater and simpler too!

Thanks in advance

Trevor

Many thanks

It just gets better. I can't use the MOREFUNC.XLL as I work in a strictly
controlled environment where it would not be possible to distribute the
Add-in

However, the SUMPRODUCT option is looking good

Regards

Trevor


"Aladin Akyurek" wrote in message
...
If you have Longre's free morefunc.xll add-in:

=COUNTIF.3D('Person 1':'Person 5'!$C8,"Off")

Otherwise:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetList&"'!C8") ,"Off"))

where SheetList refers to a range housing the relevant sheetnames in
separate cells.

Trevor Shuttleworth wrote:
How can I turn a formula like:

=COUNTIF('Person 1'!$C8,"Off")+COUNTIF('Person
2'!$C8,"Off")+COUNTIF('Person 3'!$C8,"Off")+COUNTIF('Person
4'!$C8,"Off")+COUNTIF('Person 5'!$C8,"Off")

Into (something like):

=COUNTIF('Person 1':'Person 5'!$C8,"Off") ... this gives #NAME?
=COUNTIF('Person 1:Person 5'!$C8,"Off") ... this gives #VALUE!

It doesn't have to be the COUNTIF function so long as it achieves the
same ends.

I need to be able to add more people without a vast amount of repetition.
It would look neater and simpler too!

Thanks in advance

Trevor

Thanks Peo ... and also JE and Aladin

Three answers within 30 minutes ... truly impressive. I've opted for the
SUMPRODUCT Option with the list of worksheet names on a Control Sheet.

I've actually defined a dynamic named range which allows me to home in on a
subset of the worksheets ... and make the formula more informative (I think)

=SUMPRODUCT(COUNTIF(INDIRECT("'"&PeopleList&"'!"&C ELL("address",C8)),"Off"))

This version also has the advantage that I can drag the formula down and the
cell will automatically change ... C8 to C9 to C10, etc

The named range PeopleList is defined as:
=OFFSET(Controls!$H$4,,,COUNTA(Controls!$I:$I),)

I list the sheet names in column H on the Controls sheet and then put real
names next to them in Column H

Thanks again for all the help

Trevor


"Peo Sjoblom" wrote in message
...
Assuming that the sheets are not called person1 but maybe a persons name
you need to put all sheet names in question in a range like in

person1
person2
person3
etc..

now assume you have 5 different sheet names and put them in H1:H5

then you can use

=SUMPRODUCT(COUNTIF(INDIRECT("'"&H1:H5&"'!C8"),"OF F"))


if the sheets have the same name plus a number you can use

=SUMPRODUCT(COUNTIF(INDIRECT("'Person"&ROW(INDIREC T("1:5"))&"'!C8"),"OFF"))

--
Regards,

Peo Sjoblom

(No private emails please)


"Trevor Shuttleworth" wrote in message
...
How can I turn a formula like:

=COUNTIF('Person 1'!$C8,"Off")+COUNTIF('Person
2'!$C8,"Off")+COUNTIF('Person 3'!$C8,"Off")+COUNTIF('Person
4'!$C8,"Off")+COUNTIF('Person 5'!$C8,"Off")

Into (something like):

=COUNTIF('Person 1':'Person 5'!$C8,"Off") ... this gives #NAME?
=COUNTIF('Person 1:Person 5'!$C8,"Off") ... this gives #VALUE!

It doesn't have to be the COUNTIF function so long as it achieves the
same ends.

I need to be able to add more people without a vast amount of repetition.
It would look neater and simpler too!

Thanks in advance

Trevor