Complex SUMIF

[alexmitchell]

Hi all,

I'm looking to solve a complex sumif formula. Basically if one column (C) reads "UK", I would like to sum another column. The additional complexity is in the fact that I only want to test the 4th value in column C, and then if the test is TRUE, sum the 4th value in column O. The current formula is below:

=SUM(IF(MOD(ROW($C$5:$C$120)-ROW($C$5)+2,4)=0,$C$5:$C$120="UK"),SUM(IF(MOD(ROW($O$6:$O$120)-ROW($O$6),4)=0,$O$6:$O$120,0)))

Column C looks like this:

UK
UK
UK
US
US
UK

and Column O contains values.

At the moment the formula above works, but it doesn't seem to only sum for cells which contain "UK". Can anyone help?

Many thanks

Alex

[bebo021999]

With the 4th value testing, what is criteria for TRUE value?

[daddylonglegs]

You say "every 4th cell" but which is the start row? Assuming row 5 try this

=SUMPRODUCT((MOD(ROW(C5:C200)-ROW(C5),4)=0)*(C5:C200="UK"),O5:O200)

adjust start row as required