Cannot get a formula array to return the correct count of list items

I have an array formula that extracts sequences and start time periods eg "1A, 2A .. 3B" (from the data block starting at G2 in the attached ) I have managed to compose a formula array that extracts the data required and places it at D17:D28.

What I can't fathom out is how to extract this data only when column G contains say "R4". Can it be done?

Sorry if this is not clear hope the attachment helps . The red fill shows the data.It would appear that the repeating sequence at row 103 is not picked up when the "R4" search is introduced.

The first formula

Please Login or Register  to view this content.

works fine

The second

Please Login or Register  to view this content.

fails
when I add in

Please Login or Register  to view this content.

Thanks for reading this far

Hi,

It works OK for me. Are you sure you're entering it as an array formula with Ctrl-Shift-Enter?

Just an observation, but did you mean to have different ranges, i.e. 3:848 and 4:849. This won't return an error since both ranges are the same size, but it looks unnatural.

Is the R4 supposed to be checked in column G only?

Please Login or Register  to view this content.

note: Using SUMPRODUCT eliminates need in this case to use CSE confirmation.

Did you try mine? Is it wrong?

CSE means you should use the CTRL-SHIFT-Enter keys all held down together to enter the formula. You'll then see {} brackets at either end of the formula in the formula bar telling you it is an array formula.

Hi,
SUMPRODUCT() is designed to work as an array formula and hence doesn't need to be made into one.
=SUM() and others, if you want them to operate as array formulae do need the CSE

Regards