Lookup value across a row and then countif

[joesmalls]

Hi there. I'm new the forum. Tried searching for my question but didn't have much luck and to be honest I'm not even sure how to word this properly so bare with me.

I'm using Excel 2010 to do this btw.

The first worksheet is as follows:
Column A = Employee
Column B = Department name
Column C = Active (either Yes or No)

Then in the second worksheet, what I'm trying to do is count the number of active employees for each department based on the info in the first worksheet.

What I have so far is a half-baked solution where I create Column D and use formula
CONCATENATE(B1,"-",C1).

Then in the second worksheet I can count the total number of active employees based on how many of those results in Column D are "Dept-YES". This works, but it seems like a work around that I should be able to get around.

I've tried working out the logic by paper but just can't seem to translate it into Excel using formulas. Any ideas?

[Domski]

Hi,

I think you should be able to use COUNTIFS or a pivot table to achieve what you want.

Dom

[Marcol]

Hi joesmalls

Welcome to the Forum ...

Sounds like you're looking for COUNTIFS()

See this workbook

[Azam Ali]

pivot, sumproduct, countif all could do this

see attatchmentAttachment 122614

[joesmalls]

All solutions here worked. Just wanted to mention that I was leaning in the direction of COUNTIF but wasn't sure how to enter multiple criteria. I did run into COUNTIFS when searching but I wasn't sure how to use it so I continued googling without much luck. But it makes more sense now especially being able to look at 2 examples.

And SUMPRODUCT is a new function to me but seems rather handy.

Thanks again!