Urgent help me with a Excel formula please!!

[philstanbridge]

Can some kind soul give me some Excel advice please I am desperate. I have to finish my assignment tonight and for some reason I cannot work out what the formula is I need to use.

All I need is this. To count how many "Married" in one column, "servants" in another and add them together. I cannot work it out and I'm pulling my hair out with frustration!!

 

[whitecrook]

count(a1:a26,"married") or something like that

 

[philstanbridge]

count(a1:a26,"married") or something like that

That's correct so far as one goes, but how do I get it to add the other? Sorry its not just adding it, I need to select from a table of over 4000 entries which servants were married from this list. But there are servants who are unmarried for example.

 

[chrisxuk]

Is this too simple?

EDIT: The time delay between when i refreshed the page has meant i didn't see your 2nd post philstanbridge.

 

[philstanbridge]

erm, yes I think so. This data is 10mb in size and it's a pain. It's statistical analysis and I need to find how many servants were married. Sounds simple but I'm not finding it so.

 

[chrisxuk]

haha, i guess mine was far too simple

 

[james.miller]

sumif will do it

edit: i think

 

[JC]

Is your data in a text format with columns that say "married" and "servant"
Or is the data under a heading Married and Servant with 1 or 0 as the output?


You could make a new column
=IF(A1="Married",IF(B1="Servant",1,0),0)

Then copy that formula down and take the sum of it

OR you could use a new column C:

=AND(A1="Married",B1="Servant")

which will output TRUE if the criteria is met, then in a new cell go =Countif(C1:C10000=TRUE)

hope that helps Phil

 

[philstanbridge]

Is your data in a text format with columns that say "married" and "servant"
Or is the data under a heading Married and Servant with 1 or 0 as the output?

It's text format but not straightforward. Columns say OCCUPATION, CIVIL STATUS etc. I was under the impression I need to use COUNTIF, although it may be COUNTIFS. God knows.

 

[philstanbridge]

ahhhaha! I've sorted it, but not in Excel! How bizarre. I had to use star office in the end to work it out. But thanks for your help guys! (I'm still curious mind you, love to know what the formula is in Excel). I've been on this assignment all week, and today since 9am!

 

[philstanbridge]

STUFF

hope that helps Phil

Thanks so much JC, I will try that out in the morning, I'm spent right now.

 

[BigglesPiP]

Perl working on a CSV file would be a neat option, regexp is made for this.

Where the header row is: ID, Marrital_status, slave_status, something_else;

#!/usr/bin/perl

use strict;

# Globals

my $csvfile = '/home/biggles/table.csv';

open CSVFILE, $csvfile or die "What file?\n";

my $count = 0;
foreach my $line (<CSVFILE>) {
  chomp $line;
  if ($line =~ m/^.*,'married',.*,.*$/) {
    $count++;
  } elseif ($line =~ m/^.*,.*,'servant',.*$/) {
    $count++;
  } else {
    print "WARNING: don't understand \"$line\".\n";
  }
}
print "Count is: $count\n.";

 

[Goksly]

Well I'd say if you were going for programming you'd be best off treating it like a database (select count(*) from [worksheet$] where occupation = 'Servant' and status = 'Married'). Very easily done in c#

 

[Dolph]

Well I'd say if you were going for programming you'd be best off treating it like a database (select count(*) from [worksheet$] where occupation = 'Servant' and status = 'Married'). Very easily done in c#

Or do something similar in Access to populate the data.

 

[div0]

edit - misunderstood the OP

But you could do it with the following formula:

=SUMPRODUCT(--(A1:A5000="Married"),--(B1:B5000="Servants"))