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...