IF Function issue to ignore blank rows

[ollyhughes1982]

Hi all,

I have the formula (below), in column BO, that counts streaks of different events. i.e. it counts from 1 upwards, until and event is repeated (in column BH).

=IF(C5="","",IF(COUNTIF(OFFSET(BH5,0-BO4,0):BH5,BH5)>1,ROW(BH5)-SUMPRODUCT(MAX(ROW(OFFSET(BH5,0-BO4,0):BH4)*(OFFSET(BH5,0-BO4,0):BH4=BH5))),BO4+1))

This formula works when there are no blank rows (gaps in column BH). However, I want the formula to be able to cope with the gaps. i.e. Ignore them. See image (below):

The reason for the gaps is that you can volunteer for more than one roll on any given date at each particular venue (e.g. you can be a tail walker and also a car park marshal etc. at the same event on the same day). So, I only want an entry when there is a different event - which is what I have in column BH.

I have entered the data manually as it should be (highlighted in yellow, in BP). i.e. As soon as an event is repeated, the counter resets to 1 and starts counting again, until another repeat is found.

So, essentially, in column BO I want the formula to operate as it is, but just ignoring the gaps, as if they weren’t there.

Any help much appreciated! Thanks in advance,

Olly.

 

[ollyhughes1982]

Hi. No responses to this, so wondering if I might need to include a link to the actual file, for better explanation?

 

[ollyhughes1982]

OneDrive link here: IF SUMPRODUCT Example.xlsx

It's a small file and should open straight up.

Question also posted on 'Excel Forum': Working IF and SUMPRODUCT function, but now need to ignore empty cells

Thanks.

 

[ollyhughes1982]

This has now been solved, via the above cross-post. Thanks all.