"Nested" COUNTIF's

[Nils Magnus]

Hello, I have a simple application which retrieves a list of users from
an Active Directory, and exports it to an Excel file.

Each user is on a row with columns A-E containing name, user name,
department and a few other columns. Column F and the successive columns
contain the groups the user is member of - one group name per cell.

By using COUNTIF on the department column, I can easily find out how
many users are in a given department. Similarly, by using COUNTIF on
columns F-IV, I can count how many users are in a given group (assuming
each user only can be member of the given group once, which is a safe
assumption to make).

However, I'm having some trouble with the next calculation: How do I
count the number of users _in a given department_ who are members of a
given group? I can solve this by making auxiliary columns containing a
formula, but is it possible to solve by having the entire formula in a
single cell? (this calculation needs to be done for a lot of departments
and groups, so something other than a single-cell solution would get messy).

I appreciate any input!



Regards,
Nils Magnus

 

[Guest]

=SUMPRODUCT(--(DeptRange=DeptNumber), --(GroupRange=GroupNumber))

Change DeptRange and GroupRange to whatever the range reference is for your
data (can't be and entire column unless using XL07) and DeptNumber and
GroupNumber to your criteria (or cell references that contain your criteria).

 

[Nils Magnus]

Hello,

Thanks for your reply, but unfortunately the SUMPRODUCT function needs
it array parameters to be the same dimensions. The GroupRange is over
many columns (since there's one group in each cell, and each user is a
member of many groups), so I get a #VALUE error when I try to evaluate
the function.

When I set GroupRange to just a single column, it works (but then it
would only check if the first group the user is a member of is the
correct group).


Regards,
Nils Magnus

 

[Guest]

Hi Nils,

You can create a summary area which looks like this:


Flags Jars
Acct 2 0
Admin 1 0

I put the tilte Acct in C17 and Flags in B16.
The formula in the Flags (group) - Acct (department) is

=SUMPRODUCT(($C$2:$C$9=$C17)*($F$2:$IV$9=D$16))

 

[Guest]

Sorry, I misunderstood the group range is over several columns. A small
change:

=SUMPRODUCT((DeptRange=DeptNumber)*(GroupRange=GroupNumber))

given your stated assumption that each user only can be member of the given
group once. otherwise, you could try:

=SUM((DeptRange=DeptNumber)*(SIGN(MMULT(--(GroupRange=GroupNumber),TRANSPOSE(COLUMN(GroupRange)/COLUMN(GroupRange))))))

array entered w/Cntrl+Shift+Enter (or you'll get #VALUE)

 

[Nils Magnus]

Thanks JMB and Shane, your solution works great!


Regards,
Nils Magnus