Hi,
So I'm pretty sure INDEX MATCH or a variation of will be able to help but I can't quite get it right.
- In column H I have a list of towns.
- In column Y I have a combination of 1's and 0's going down the spread sheet.
- I want to be able to record a town if there is a 1 in the corresponding cell in column Y.
- I want to be able to record how many of each town is recorded.
H I Y
[TABLE="width: 500"]
<tbody>[TR]
[TD]Towns[/TD]
[TD]Counties[/TD]
[TD]Response[/TD]
[/TR]
[TR]
[TD]Birmingham[/TD]
[TD]WM[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Oxford[/TD]
[TD]Oxfordshire[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Kettering[/TD]
[TD]Northamptonshire[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Birmingham[/TD]
[TD]WM[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]London[/TD]
[TD]London[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Manchester[/TD]
[TD]GM[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Liverpool[/TD]
[TD]Merseyside[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Milton Keynes [/TD]
[TD]Buckinghamshire[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Oxford[/TD]
[TD]Oxfordshire[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
My Results would therefore be:
Birmingham 2
Kettering 1
London 1
MK 1
Oxford 1
So I'm pretty sure INDEX MATCH or a variation of will be able to help but I can't quite get it right.
- In column H I have a list of towns.
- In column Y I have a combination of 1's and 0's going down the spread sheet.
- I want to be able to record a town if there is a 1 in the corresponding cell in column Y.
- I want to be able to record how many of each town is recorded.
H I Y
[TABLE="width: 500"]
<tbody>[TR]
[TD]Towns[/TD]
[TD]Counties[/TD]
[TD]Response[/TD]
[/TR]
[TR]
[TD]Birmingham[/TD]
[TD]WM[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Oxford[/TD]
[TD]Oxfordshire[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Kettering[/TD]
[TD]Northamptonshire[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Birmingham[/TD]
[TD]WM[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]London[/TD]
[TD]London[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Manchester[/TD]
[TD]GM[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Liverpool[/TD]
[TD]Merseyside[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Milton Keynes [/TD]
[TD]Buckinghamshire[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Oxford[/TD]
[TD]Oxfordshire[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
My Results would therefore be:
Birmingham 2
Kettering 1
London 1
MK 1
Oxford 1