Trying to count households from a list of customers

Hello!

I've attached a spreadsheet to help give an idea of what I'm trying to do. Is there a way to get excel to figure out how many households enjoy, say, fishsticks, from a list of customers? I was thinking that maybe something that could check the address in, say, row 2 against the row above it and if so, count the whole household as one (pretty much what I'm doing manually), but Something in my mind keeps saying that there's go to be a better way, but no matter how much hair I pull out, I still can't come up with a solution :P

Testbook.xlsx

It came up with the right answer, but when I tried to add more names & addresses to it, it stayed at 3. Maybe I edited the formula wrong?

Didn't I? Maybe my brain is fried. I added three names, two addresses (and two yes households), and changed the end of my range to the appropriate row, but it still reads 3.

Testbook.xlsx

@softsurv:

i am very sorry - my formula was completely utterly wrong. when i looked at @donkeyote's formula, i realised the error.

@DonkeyOte: I apparently wasn't paying attention when I had Excel fill those addresses, so you're right, it should have been 8. Your formula is perfect. I really hope I don't flub it up when I change it for work tomorrow. It would make my department's job so much less time consuming. Thank you so much!

@icestationzbra: No worries, man. I've been trying unsuccessfully to make Excel do this all week, so I appreciate you taking a look at it anyway.

@Russell Dawson: Correct me if I'm wrong, but that only appears to be counting the number of cells in the range. Even when I changed the range to encompass all of the data, it just seems to count "1" for each cell in the range.