I think I need basic help with a formula

[Bootzie]

I am trying to setup a spread, sheet to count how many different branches of a certain coffee shop I visit. I enter where, when I go. How much I spend etc, but the problem is getting the sheet to count how many unique shops I visit from many re visits. I have tried to enter a range of where the info will eventually go. But it doesnt like dealing with blank cells. The formula (=SUMPRODUCT(1/COUNTIF(B5:B22,B5:B22)) I am using came from a help film on you tube but I have to keep changing the range after every visit. Is it possible to automate this? 

I am using laptop, windows 10, excel 2010

[#2 · Accepted solution]

@Bootzie 

One of variants

=SUMPRODUCT(1/COUNTIF(B4:INDEX(B4:B1000, COUNTA(B4:B1000)),B4:INDEX(B4:B1000, COUNTA(B4:B1000))))

[#3]

@Sergei Baklan 

Thank you for that, I have been struggling for some time.

regards

bootzie

[#4]

@Bootzie , you are welcome