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?
Hi,
I think you should be able to use COUNTIFS or a pivot table to achieve what you want.
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Hi joesmalls
Welcome to the Forum ...
Sounds like you're looking for COUNTIFS()
See this workbook
If you need any more information, please feel free to ask.
However, if this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody! ....
Also
If you are satisfied by any members response to your problem please consider using the small Star icon botom left of thier post to show your appreciation.
pivot, sumproduct, countif all could do this
see attatchmentAttachment 122614
Azam
If you want to say Thank you to a member, click the reputation icon (Star) in the left bottom of the post.
For prompt answer, be descriptive, concise, short, direct, and to-the-point.
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!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks