Excel - COUNT Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need to calculate the number of female board members and directors for each company, also the % of females and how they compare to number and % of men in the same firms. Only small firms should count and only board members and relevant associating functions.
Solved by K. D. in 41 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 06/05/2018 - 01:49
Hello
Excelchat Expert 06/05/2018 - 01:50
hello!
Excelchat Expert 06/05/2018 - 01:50
how are you?
User 06/05/2018 - 01:50
Great, thanks for asking
User 06/05/2018 - 01:50
Yourself?
Excelchat Expert 06/05/2018 - 01:50
I'm good :) thanks
User 06/05/2018 - 01:51
Good to hear
Excelchat Expert 06/05/2018 - 01:51
are you able to share your worksheet where you need to do the counting?
User 06/05/2018 - 01:51
Yes
User 06/05/2018 - 01:51
https://drive.google.com/open?id=1JJ8oglbnREdjCv71ilV7bks0SBkjgQw3
User 06/05/2018 - 01:51
This is the link
User 06/05/2018 - 01:51
It's quite a big file
Excelchat Expert 06/05/2018 - 01:51
okay, will download it now
User 06/05/2018 - 01:51
But it needs to be sorted
User 06/05/2018 - 01:51
I tried sending it
User 06/05/2018 - 01:51
Sorted
User 06/05/2018 - 01:51
But it didn't work
Excelchat Expert 06/05/2018 - 01:52
I see, how many MB is this?
Excelchat Expert 06/05/2018 - 01:52
it's still loading as we speak
User 06/05/2018 - 01:53
Alright
Excelchat Expert 06/05/2018 - 01:54
Would you know how big the file is?
User 06/05/2018 - 01:54
Without deleting the unccessary columns
User 06/05/2018 - 01:54
I believe it is 50MBs?
Excelchat Expert 06/05/2018 - 01:55
are you able to send me a trimmed down version of the worksheet?
Excelchat Expert 06/05/2018 - 01:55
i mean excluding the unnecessary columns. If that wouldn't be much of a bother, that is
User 06/05/2018 - 01:56
Sure I will give it a go
User 06/05/2018 - 01:56
This
User 06/05/2018 - 01:56
Link
User 06/05/2018 - 01:56
Should work
Excelchat Expert 06/05/2018 - 01:56
Great! Thank you!
User 06/05/2018 - 01:56
https://drive.google.com/open?id=15mcn8FYI75ruThtJnulEvcxNnveUFeL2
Excelchat Expert 06/05/2018 - 01:58
Anyway, have you tried using pivot table for this?
Excelchat Expert 06/05/2018 - 01:58
still downloading it now... but would like to probe further so we may do something while we wait
User 06/05/2018 - 01:58
Yeah I don't really know how to use pivot tables haha
User 06/05/2018 - 01:59
Btw what is the name of the file you are downloading? This one is 16MB
Excelchat Expert 06/05/2018 - 01:59
small companies filtered
Excelchat Expert 06/05/2018 - 01:59
how about take a sample slice of the data set and send it to me via attachment?
Excelchat Expert 06/05/2018 - 02:00
Oh, never mind
Excelchat Expert 06/05/2018 - 02:00
completely downloaded just now
User 06/05/2018 - 02:01
Alright perfect
Excelchat Expert 06/05/2018 - 02:01
okay, let's do this together. i'll detail the steps by steps now
Excelchat Expert 06/05/2018 - 02:01
are you on excel?
User 06/05/2018 - 02:02
Yeah, but I have trouble opening both files that's also a problem
User 06/05/2018 - 02:02
It' way too slow
User 06/05/2018 - 02:02
Don't think my laptop
User 06/05/2018 - 02:02
Can work with files this big
Excelchat Expert 06/05/2018 - 02:02
aww that's unfortunate
Excelchat Expert 06/05/2018 - 02:02
are you okay if i send to you the final product instead
Excelchat Expert 06/05/2018 - 02:03
could you please see the document preview on the right
Excelchat Expert 06/05/2018 - 02:03
just want to make a few clarifications before i proceed with it
User 06/05/2018 - 02:03
Would you be able to calculate the metrics I need?
User 06/05/2018 - 02:03
Sure
User 06/05/2018 - 02:03
Alright
Excelchat Expert 06/05/2018 - 02:04
yeah, just want to clarify what you need to see in the output table
User 06/05/2018 - 02:04
Yeah I see you just wrote Company Name
Excelchat Expert 06/05/2018 - 02:05
is this in line with your expectation?
Excelchat Expert 06/05/2018 - 02:05
please edit as you deem fit
User 06/05/2018 - 02:07
Yeah
User 06/05/2018 - 02:07
This is perfectly fine
User 06/05/2018 - 02:07
I would only add
User 06/05/2018 - 02:07
Overall board size
User 06/05/2018 - 02:07
So the total of M + F
Excelchat Expert 06/05/2018 - 02:08
like this?
Excelchat Expert 06/05/2018 - 02:08
do i have to include only specific functions?
Excelchat Expert 06/05/2018 - 02:09
like exclude traders in the count
Excelchat Expert 06/05/2018 - 02:10
haha, my excel just crashed too
Excelchat Expert 06/05/2018 - 02:10
wait, i'll restart
User 06/05/2018 - 02:11
No worries
User 06/05/2018 - 02:11
But yes
User 06/05/2018 - 02:11
Other functions should be excluded
User 06/05/2018 - 02:11
Other company sizes as well
User 06/05/2018 - 02:11
Only small are relevant
Excelchat Expert 06/05/2018 - 02:12
how do i filter small company size?
Excelchat Expert 06/05/2018 - 02:13
there are three columns available though
Excelchat Expert 06/05/2018 - 02:13
no company size - has this been filtered to exclude big companies already?
User 06/05/2018 - 02:13
Oh sorry
User 06/05/2018 - 02:13
Everything
User 06/05/2018 - 02:13
Is already
User 06/05/2018 - 02:13
Filtered
User 06/05/2018 - 02:13
In this doc
User 06/05/2018 - 02:14
So only the count is to be done
Excelchat Expert 06/05/2018 - 02:14
Got it! thanks
Excelchat Expert 06/05/2018 - 02:14
will get back to you asap. this might take a while
User 06/05/2018 - 02:16
Sure
User 06/05/2018 - 02:16
Thanks a lot btw!
Excelchat Expert 06/05/2018 - 02:19
could you please confirm this?
[Uploaded an Excel file]
Excelchat Expert 06/05/2018 - 02:19
Are you sure about the managing director?
Excelchat Expert 06/05/2018 - 02:20
and the GM?
User 06/05/2018 - 02:20
Yeah that looks fine
Excelchat Expert 06/05/2018 - 02:20
okay, got it
User 06/05/2018 - 02:21
Actually sorry
Excelchat Expert 06/05/2018 - 02:21
yes?
User 06/05/2018 - 02:21
MD cn be included
Excelchat Expert 06/05/2018 - 02:22
I thought so :) Okay
User 06/05/2018 - 02:22
Thanks!
Excelchat Expert 06/05/2018 - 02:26
Please see attached
[Uploaded an Excel file]
Excelchat Expert 06/05/2018 - 02:26
sorry i can only send the results as the file is too big
Excelchat Expert 06/05/2018 - 02:27
Hi! Are you still there?
User 06/05/2018 - 02:28
Yeah, still here!
User 06/05/2018 - 02:28
Opening the file now :))))
Excelchat Expert 06/05/2018 - 02:28
Good, thanks
Excelchat Expert 06/05/2018 - 02:29
any luck so far?
User 06/05/2018 - 02:29
Yeah just opened it
User 06/05/2018 - 02:30
It
User 06/05/2018 - 02:30
Looks
User 06/05/2018 - 02:30
Perfect!
Excelchat Expert 06/05/2018 - 02:30
Happy to hear that!
User 06/05/2018 - 02:30
Thanks a lot!
Excelchat Expert 06/05/2018 - 02:30
You're welcome :)
Excelchat Expert 06/05/2018 - 02:30
If you don't have any more clarifications at this point, you may end the session by hovering over the TIME REMAINING section of this chat window and select END SESSION
User 06/05/2018 - 02:30
Could I just bother you with one really
User 06/05/2018 - 02:30
Simple thing?
Excelchat Expert 06/05/2018 - 02:31
sure, what about?
User 06/05/2018 - 02:31
Actually no worries
User 06/05/2018 - 02:31
I will do it
User 06/05/2018 - 02:31
Myself
User 06/05/2018 - 02:31
Thank you very much!
Excelchat Expert 06/05/2018 - 02:31
Haha okay :))

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc