Help counting entries on another sheet

dave.mcpherson56751

Hi,

I am trying to count the amount of entries of another sheet that match the name of a row on my master sheet. I have tried to do something with count Ifs and match@row but have got a bit lost.

So on my master sheet i have the name Basingstoke, beside that i have added a column saying how many times this appears on my secondary sheet.

Can any one help with a formula that looks at the name in the master sheet row and then does a count?

Really appreciate any help.

Thank you

Dave

 

reshma

Hi,

I'm trying to do the exact same thing. Would appreciate any responses.

Andrée Starå

Hi Dave & Reshma,

Try this.

The range (Dave_Reshma - Sheet 2 Range 1) is the whole column on the second sheet that has the Basingstoke reference in it.

Main is the column with the reference to what you want to count in the second sheets range.

The formula should be in the Count column on the master sheet and if you have more references that you want to count you would fill the formula down, and because we have the @Row function, it will look at the Main column and what it should count from the second sheet.

=COUNTIF({Dave_Reshma - Sheet 2 Range 1}; Main@row)    

The same version but with the below changes for your and others convenience.  

=COUNTIF({Dave_Reshma - Sheet 2 Range 1}, Main@row)

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

I hope this helps you!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

dave.mcpherson56751

Hi Andree,

that has got things working, thank you. Probably a silly question but my column "Main" is named Site Name. When i type in Site name to the formula it doesnt accept it, but i have created another column called site and it works. does it not accept columns with a space between words or is there something i should do to fix this?

Thank you so much for your help

Dave

Andrée Starå

Happy to help!

It's because it needs to be in brackets when you have spaces or numbers in the column name.

Try this.

=COUNTIF({Dave_Reshma - Sheet 2 Range 1}; [Site Name]@row)    

The same version but with the below changes for your and others convenience.  

=COUNTIF({Dave_Reshma - Sheet 2 Range 1}, [Site Name]@row)

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Best,

Andrée

dave.mcpherson56751

Working perfectly, thank you so much for your help and advice.

Dave

Andrée Starå

I'm always happy to help!

Best,

Andrée