Sumproduct

[sodhouse]

Hi

I have two columns.
Column A = Departments
Column B = Non working days (shown as x)

sales x
sales
finance
sales
finance x

I need to show how many sales staff we have available, taking into
account those who do not work that day. So in the example, I have 3
sales staff, one isn't at work and so my answer is 2.

I tried =SUMPRODUCT(--(A1:5="Sales")-(--(B1:5="X")))

which gives me all the members of the sales team (3) minus ALL the non
working days (2). Is there any way to count just the sale staff who
aren't at work, or just the finance staff who aren't at work?

Thanks in advance for any help - this is beyond me!!

 

[JE McGimpsey]

One way:

Count of Sales staff who aren't at work:

=SUMPRODUCT(--(A1:A5="sales"),--(B1:B5="x"))

Count of Finance staff who aren't at work:

=SUMPRODUCT(--(A1:A5="finance"),--(B1:B5="x"))