Sumproduct

S

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!!
 
J

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"))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Work and staff schedule 1
=SUMPRODUCT revisited 7
Sumproduct, array, if to multiply mixed series of numbers 8
SUMIF formula required to search for 2 diff values in 2 diff colum 7
Create a yes no message on a command button on click 0
Match a value to multple criteria both in rows and columns 3
Excel Macro to use cells as a autofilter 0
PLEASE HELP! Return a Value Using Multiple Sources 4

Top