Searching for alphanumeric values

**seananton** · 05-08-2009, 09:10 AM

I have a sheet containing the names and details, such as religion, of 630 inhabitants of Ballymena workhouse in 1847, the worst year of the Irish famine.
I would like to set up a series of searches under the "religion" column to find out how many were 'Presbyterian', 'Rom.Catholic', 'Est.Church'.

I realise that this is probably very simple but I have had very little experience with Excel.

Sean.

**NBVC** · 05-08-2009, 09:15 AM

Check out the Countif() function in Excel Help.

e.g.

=Countif(A:A,"Presbyterian") counts number of Presbyterian entries in column A

Edit:

And Sumif would sum of values in adjacent column to the criteria...

e.g.

=SUMIF(A:A,"Presbyterian",B:B)

**seananton** · 05-08-2009, 11:14 AM

Thanks, that led me to the answer.
Some 25% of the inhabitants died and now I would like to search for a combination of "Presbyterian" and "Dead" in columns 'H' and 'P'

Thanks,

Sean

**NBVC** · 05-08-2009, 11:19 AM

For multiple criteria use Sumproduct()

e.g. =Sumproduct(--(H2:H100="Presbyterian"),--(P2:P100="Dead"),S2:S100)

where S2:S100 is the range to sum up... if you just want to count, take that range out of the formula.

Note also: with Sumproduct you have to use defined ranges like H2:H100 not full ranges like H:H (unless you use XL2007)