1

I have a table of Stores (well, for this example anyway):

Store_ID Store_Name  ...
1        High Street
2        Low Street
...

I have another table of Employees:

Employee_ID Store_ID Is_Active
1           1        Y
2           1        Y
3           1        N
4           2        N
5           2        N
6           2        Y

I would like to count the number of active employees for a given store.

In other words, I want my output table to look like:

Store_ID Active_Employees
1        2
2        1

If I simply want to count the number of employees at a store, given that column A contains the Store_ID I can do so using an array formula:

{=SUM(IF($A2=Employees!$B$2:$B$1048576, 1, 0))}

However, when I attempt to take activity into account:

{=SUM(IF(AND($A2=Employees!$B$2:$B$1048576, Employees!$C$2:$C$1048576="Y"), 1, 0))}

then I get 0 for everything.

I'm guessing that using two separate array ranges is not stepping them along together, as I expected.

So essentially I have two questions:

  • is it possible to move two arrays along in step using an array formula?
  • if not, how can I achieve what I'm after?

EDIT: This appears to work correctly if I use nested IF statements rather than AND. I am very confused...

1 Answer 1

1

It doesn't need to be an array formula to do this. It can be done with a COUNTIFS formula.

For instance the below formula will count the number of instances from given range on the Employees sheet which meet the Store_ID from cell 'A2' AND equals 'Y' in column C of the Employees sheet.

=COUNTIFS(Employees!$B$2:$B$1048576,A2,Employees!$C$2:$C$1048576,"Y")

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .