Count every fourth

[agibsonsw]

I want to count how many of every fourth cell in a column end with "IM", ignoring any extra spaces following this text.

To assist in this process I have named the collection of cells 'data'.

every4.png

This is array entered but gives #VALUE. I assume it is the use of TRIM on an array/range (and RIGHT) that is problematic?

A further question is, how can I create this range as dynamic, so that entering some text in another cell three further down will include this cell in the range? Derp.. having moved the formula out of the way, into another column! ;)

[HansV]

TRIM(...) works OK with a contiguous range, but not with a discontiguous one.

=SUM(IF(RIGHT(TRIM(A1:A12),2)="IM",1))

confirmed with Ctrl+Shift+Enter will return 3 in your example. Or as an ordinary formula:

=SUMPRODUCT(--(RIGHT(TRIM(A1:A12),2)="IM"))

To ensure that only cells in rows 1, 4, 7, ... are counted, again as an ordinary formula:

=SUMPRODUCT((RIGHT(TRIM(A1:A12),2)="IM")*(MOD(ROW(A1:A12),3)=1))

[agibsonsw]

Thank you very much Hans.