Default count how many times a value is changed in a table ignoring duplicates

Hello Everyone, sorry if this was answered already but I could not locate it.
Assuming that a,b,c,d are people, 1-9 are dates, "1" is when I have them present, "h" when they are a home. I need to know how many times each one in a given period has been at home.
I need the formula to count how many times in a table(row) a value is changing from "1" to "h". If "h" is in the next cell it should be considered as one time.


0 1 2 3 4 5 6 7 8 Should look like
a h 1 1 1 h h h 1 2
b h 1 h 1 1 h h 1 3
c 1 1 1 1 h 1 1 1 1
d h 1 1 1 1 h h 1 2

Thanks in advance

use this:

=SUMPRODUCT((B2:H2="h")*(C2:I2=1))

Note that the ranges OVERLAP, they are NOT the same.

Also the formula is set to look for h then 1, as shown in your data, and not 1 followed by h, as indicated in your description!!

Administrative Note:

• We would love to continue to help you with your query, but first, before we can proceed…
• Please see Forum Rule #8 about cross-posting and adjust accordingly...
• Also see, A message to forum cross posters

I've just seen the cross post. Thanks for wasting my time, Galgo... You already have your answer, it seems.