Muskie - HELP!

Status
Not open for further replies.

NoFinPhish

Mighty Cat Slayer
Joined
Jan 17, 2003
Messages
7,190
..or any other Excel guru's

A long list of data, hundreds and hundreds of lines.
Simplified to the list below to make it easier:

Code:
[b]Name Country Grade Gender[/b]
Bob   France   D     Male
Tom   UK       D     Male
Sally UK       B     Female
Jane  Holland  C     Female
Pete  France   E     Male
John  UK       B     Male
Sarah Belgium  C     Female
Fred  UK       D     Male

etc......

I want to get a count of how many people from the UK, that are male, are grade "D"
And how many women from France are grade "B"

Whats the best way?

There are 11 countries, half a dozen or so grades and some data is missing so would help if the cells with missing data can be counted
 

darrengeorge

speci-ish
Joined
May 19, 2004
Messages
2,474
Ever thought about using a database instead of spreadsheet? That sort of task is so much simpler with SQL...
 

NoFinPhish

Mighty Cat Slayer
Joined
Jan 17, 2003
Messages
7,190
Can't change the data source.
Missus gets it sent to her, she will have to count manually unless I can come up with something

I can use COUNTIF, thats about my limit!
 

its_lazarus

a reliable friend
Joined
Jun 20, 2007
Messages
204
Can't you just click the top of the letter column and do an a-z sort then check what record has the bottom A etc?

copy and paste to new sheets so you have a page for each letter, then do a-z sorts on the country.
 

NoFinPhish

Mighty Cat Slayer
Joined
Jan 17, 2003
Messages
7,190
Yes, had her doing that already
Thing is though, there could be HUNDREDS of each grade

She also might want to do this again sometime so it would be a damn site easier to find a formula

Surely a few COUNTIFs and the of IF or AND could be used....
 

ubat

Regular member
Joined
Dec 19, 2005
Messages
523
something like:

SUBTOTAL(3[COUNTA adds up number of entries],[first box{eg G10}]:[second box{eg G990}]
to look like
=SUBTOTAL(3,G10:G990)
 

muskrat

Havin' a Waggle
Site Supporter
Joined
Oct 26, 2004
Messages
7,262
I would probably use a fifth column with the AND function like this:

=IF(AND(B1="UK",C1="D",D1="Male"),1,0)

Which would show a 1 where all three are true, otherwise a 0.
Then you can just total the column.

Hope this helps
 

NoFinPhish

Mighty Cat Slayer
Joined
Jan 17, 2003
Messages
7,190
Aaaah!

Thanks Muskie
I'll have a go with that, but have also just given the task to a VBA guru at work [:)]
 
Status
Not open for further replies.
Top