count entries if they do not match previous row

[tdlabrown]

Hi, I’m trying to make a table that takes data from a spreadsheet as I enter it and puts it in a table. I need to count clients of different demographic groups for analysis, but sometimes we might have several entries for the same client.

So, I Need a function that Counts
1) If an entry in Column C=”W”, AND
2) If an entry on that same row, but in Column B (the client’s ID number) is NOT the same as in the previous row of that same column.

Attached is an example with the basic info I am trying to analyze. Any help is appreciated 

[mcmahobt]

Two questions,

1) Is your example provided within the workbook flawed? I am seeing a "B" in the race column instead of a "W" in C21.
2) Would you allow a helper column if that column can be hidden?

[tdlabrown]

Mcmahobt, yes, I meant to change that cell to a "W" for the example but forgot (whoops). A helper column is a great idea, and I think I can achieve that, but I would prefer not to have a helper column because I might not always be in charge of this spreadsheet. As such, I need everything in this spreadsheet to be apparent to the lay user, with exception to the few functions I need to use. But, if it's not feasible, I might have to use the helper column.

[bebo021999]

I tried with this logic:

1) Count unique IDs with W. This returns 2

2) Count unique-distinct-IDs. There are 3 in row 2, 21 and 22

Final result: count 5

Array Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[bebo021999]

OK. With your correction as per #3:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[mcmahobt]

Slightly different way than bebo's, important to note that both of our solutions are array formulas and entered with CSE:

Please Login or Register  to view this content.

Hope this helps!

[tdlabrown]

Bebo and Mcmahobt, Thank you! Both work perfectly!

[tdlabrown]

Bebo and Mcmahobt, Thank you! Both work perfectly!

[tdlabrown]

If I can add one more problem, I put the code in for all my different demographic groups and now need a cell for clients of mixed race.

Basically the criteria would be: NOT W, B, NH/PI, AI/AN, Asian, or Blank
I attached a new example.

cleintdata.xlsx