Need a formula to count how many entries i have with a incemental value of 2

Ok, so this is gonna be hard to explain, as im not native in english, but i am certainly gonna try my best

Lets say i have this list, i want to find out how many streetnames are identical, and has a value of either +2 or -2

Obviously in the relatively small example below, its 0, but i have a list containing 18000 adresses, and i have to visit them all, sooner or later.
And id like to know how many of these adresses who has a neighbour adress, and in my country the left side of the street has oddnumbers and the other side has even numbers..

Is there a way to count how many "neighbouring" adresses there are?


Zip Not relevant City Street name Number
2700 Københavns kommune Brønshøj Arkaderne 42
2400 Københavns kommune København Bispebjerg Bakke 23
2400 Københavns kommune København Bygmestervej 5
2500 Københavns kommune Valby Gåsebæksvej 16
2300 Københavns kommune København Kløvermarksvej 70
2100 Københavns kommune København Lyngbyvej 172
1051 Københavns kommune København Nyhavn 6
2400 Københavns kommune København Rentemestervej 23
1453 Københavns kommune København Sankt Peders Stræde 49
2300 Københavns kommune København Sundbyvester Plads 2
2300 Københavns kommune København Sundparken 16

to count would be interesting as if on a street there were addresses at 2 ,4, 6, 8, 10 what would you wish to return?

1) A flag on each line
2) a count of 5
3) a count of 10

countifs(column with street,street on row,column with city, row with city, column with number, row with number +2)+countifs(column with street,street on row,column with city, row with city, column with number, row with number -2)

might give you some indication

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

ehm, ill try that later..

Id like to know how many there are, an exact count (no flags)

I guess what could be done is something like this, if it can be done for two entire columns...

If a1 = a2 & b1 = b1+2




I just need it in excel language...

so i attached a minor sample of a testwork book, where formulas can be tested.

I think thats what you require

i believe the count should be 7 on this one, but it depends on wether the formula counts both ways, or dosnt count the first one with it. in anycase with this small sample the count value could be verified either way.

So the logic i need to make is.


so i need assistance in how to make the formula count how many occurances of same streetname, and only 2 in value increase in the number..

is it possible?

Originally Posted by AliGW

This is the data you have given us:

Excel 2016 (Windows) 32 bit

	A	B
1	vej	nr
2	Abilgårgsvej	2
3	Abilgårgsvej	4
4	Abilgårgsvej	6
5	Abilgårgsvej	9
6	Abilgårgsvej	11
7	Peter Bangs Vej	13
8	Peter Bangs Vej	15
9	Valbygade	26
10	Valbygade	32

Sheet: Ark1

What is the logic telling you that the count should be 7? What exactly are you counting?

Hi,

The logic is that for the same name in the address, different numbers are 2.
I have highlighted with different colors.

so is it possible?

Hi

I suppose the data is sorted.

Try this formula in C2 and copy down

Formula:

Please Login or Register  to view this content.

Thank you very much Jose... it worked, and i could easily do a count of "yes" afterwards..

thank you