Excel formula novice!

[jonathan]

Hi I am trying to learn more about Excel formulas, but it's a hard thin
to learn from a book! Therefore, am appealing for expert help.

I am trying to write a formula that will match a word in one colum
with a word in another and give me a count for that.

For example, here is some of the data I have collected from a survey:

ethniticy reason for contact
white_british refuse
white_british street_lighting
white_british refuse
asian_brit job_application
white_british refuse
white_british street_lighting
white_euro refuse
white_british refuse
white_british job_application
white_british refuse

So, what formula do I write to match the word "white_british" wit
"refuse"? I know the count is 5 but I cannot get a formula to calculat
this automatically for me.

Any help would be greatly appreciated.

Cheers

 

[Paul]

Hi I am trying to learn more about Excel formulas, but it's a hard thing
to learn from a book! Therefore, am appealing for expert help.

I am trying to write a formula that will match a word in one column
with a word in another and give me a count for that.

For example, here is some of the data I have collected from a survey:

ethniticy reason for contact
white_british refuse
white_british street_lighting
white_british refuse
asian_brit job_application
white_british refuse
white_british street_lighting
white_euro refuse
white_british refuse
white_british job_application
white_british refuse

So, what formula do I write to match the word "white_british" with
"refuse"? I know the count is 5 but I cannot get a formula to calculate
this automatically for me.

Any help would be greatly appreciated.

Try a formula such as
=SUMPRODUCT((A1:A100="white_british")*(B1:B100="refuse"))
Adjust the ranges to suit the position of your data.

Maybe better for you would be to use something like
=SUMPRODUCT((A1:A100=A123)*(B1:B100=B123))
where A123 and B123 are cells containing the text you want to look for.

 

[jonathan]

A million thank you's. It seems to be working.

Thanks for talking the time to help.

Have a Merry Christmas and a kicking New Year - cos I will if I can
get this survey analysis sorted by the end of today.

Cheers.

 

[jonathan]

What happens if I then want to match "other" ethnicity with their reason
for calling?

for example

enthnicity other reason
white_british refuse
australian refuse
brazilian street_lighting
welsh refuse

how do I match those ethnicity in column b which can be ANYTHING with
"refuse"?

Again, any help would be greatly appreciated!

 

[ToweringQs]

You data seem like good candidates for a Pivot Table.

Count of reason reason
ethniticy job_application refuse street_lighting Grand Total
asian_brit 1 1
white_british 1 5 2 8
white_euro 1 1
Grand Total 2 6 2 10