Counting...

Hello - I have a counting question:
Given the following data -
use table1 use table2 use table 3 use table 4 use
table 5 etc..
dept1 contact TRUE FALSE FALSE FALSE FALSE
dept1 contact TRUE FALSE FALSE TRUE FALSE
dept1 contact FALSE FALSE FALSE FALSE TRUE
dept2 contact TRUE TRUE TRUE TRUE FALSE
dept2 contact FALSE TRUE FALSE FALSE FALSE
etc..

I need to subtotal table use by department, counting all the TRUES. That's
not too hard by adding a column using the COUNTIF function, which I initially
did, but I end up double counting. I'm looking for a way to count an
instance of a table use for a department despite how many contacts in that
department use it.

Any help is greatly appreciated... PS: I really don't know VBA
Thanks!!!

Patrick

Do you mean that Dept 1, Table 1 should count as 1 or 2 in your example
below?
Is 'contact' a random name each time (ie will a change of contact change the
equation)?


Steve


"Patrick G" <PatrickG@discussions.microsoft.com> wrote in message
news:D81E7DE0-AF53-43C0-9982-33C347B3ED24@microsoft.com...
> Hello - I have a counting question:
> Given the following data -
> use table1 use table2 use table 3 use table 4 use
> table 5 etc..
> dept1 contact TRUE FALSE FALSE FALSE
> FALSE
> dept1 contact TRUE FALSE FALSE TRUE
> FALSE
> dept1 contact FALSE FALSE FALSE FALSE TRUE
> dept2 contact TRUE TRUE TRUE TRUE
> FALSE
> dept2 contact FALSE TRUE FALSE FALSE
> FALSE
> etc..
>
> I need to subtotal table use by department, counting all the TRUES.
> That's
> not too hard by adding a column using the COUNTIF function, which I
> initially
> did, but I end up double counting. I'm looking for a way to count an
> instance of a table use for a department despite how many contacts in that
> department use it.
>
> Any help is greatly appreciated... PS: I really don't know VBA
> Thanks!!!

Yes, a combination of dept1 table1 should only be counted once.. not twice.
And, yes, contact is a random name that should have no bearing on the
equation.

"Steve" wrote:

> Patrick
>
> Do you mean that Dept 1, Table 1 should count as 1 or 2 in your example
> below?
> Is 'contact' a random name each time (ie will a change of contact change the
> equation)?
>
>
> Steve
>
>
> "Patrick G" <PatrickG@discussions.microsoft.com> wrote in message
> news:D81E7DE0-AF53-43C0-9982-33C347B3ED24@microsoft.com...
> > Hello - I have a counting question:
> > Given the following data -
> > use table1 use table2 use table 3 use table 4 use
> > table 5 etc..
> > dept1 contact TRUE FALSE FALSE FALSE
> > FALSE
> > dept1 contact TRUE FALSE FALSE TRUE
> > FALSE
> > dept1 contact FALSE FALSE FALSE FALSE TRUE
> > dept2 contact TRUE TRUE TRUE TRUE
> > FALSE
> > dept2 contact FALSE TRUE FALSE FALSE
> > FALSE
> > etc..
> >
> > I need to subtotal table use by department, counting all the TRUES.
> > That's
> > not too hard by adding a column using the COUNTIF function, which I
> > initially
> > did, but I end up double counting. I'm looking for a way to count an
> > instance of a table use for a department despite how many contacts in that
> > department use it.
> >
> > Any help is greatly appreciated... PS: I really don't know VBA
> > Thanks!!!
>
>
>

Patrick

If I understand correctly, insert this in the row where departments change
and adjust the ranges

=IF(COUNTIF(C5:C7,TRUE)>=1,1,0)

OR, without using IF

=(COUNTIF(G5:G7,TRUE)>=1)*1

HTH
Steve


"Patrick G" <PatrickG@discussions.microsoft.com> wrote in message
news:7969C577-A81B-401F-B3D9-69FE5E44178C@microsoft.com...
> Yes, a combination of dept1 table1 should only be counted once.. not
> twice.
> And, yes, contact is a random name that should have no bearing on the
> equation.
>
> "Steve" wrote:
>
>> Patrick
>>
>> Do you mean that Dept 1, Table 1 should count as 1 or 2 in your example
>> below?
>> Is 'contact' a random name each time (ie will a change of contact change
>> the
>> equation)?
>>
>>
>> Steve
>>
>>
>> "Patrick G" <PatrickG@discussions.microsoft.com> wrote in message
>> news:D81E7DE0-AF53-43C0-9982-33C347B3ED24@microsoft.com...
>> > Hello - I have a counting question:
>> > Given the following data -
>> > use table1 use table2 use table 3 use table 4
>> > use
>> > table 5 etc..
>> > dept1 contact TRUE FALSE FALSE FALSE
>> > FALSE
>> > dept1 contact TRUE FALSE FALSE TRUE
>> > FALSE
>> > dept1 contact FALSE FALSE FALSE FALSE
>> > TRUE
>> > dept2 contact TRUE TRUE TRUE TRUE
>> > FALSE
>> > dept2 contact FALSE TRUE FALSE FALSE
>> > FALSE
>> > etc..
>> >
>> > I need to subtotal table use by department, counting all the TRUES.
>> > That's
>> > not too hard by adding a column using the COUNTIF function, which I
>> > initially
>> > did, but I end up double counting. I'm looking for a way to count an
>> > instance of a table use for a department despite how many contacts in
>> > that
>> > department use it.
>> >
>> > Any help is greatly appreciated... PS: I really don't know VBA
>> > Thanks!!!
>>
>>
>>