Sumif function for non consecutive cells in sum and criteria

[jblaz]

I'm having issues figuring out how to setup a sumif function with a range that is not consecutive. For each day we have a new column, so setting up a named range is not optimal, a formula that could essentially be cut and pasted to each new day would be the only solution that would save any time.

I need to add cells EK6, EK11, EK16, EK21...EK56, EK61 if they have a value of 2 in the cell above it, being EK5, EK10, EK15, EK20...EK55, EK60 respectively. Is there any way to setup a reusable formula to accomplish this task?

[Ron Coderre]

This regular formula sums according to the rules you posted:
=SUMPRODUCT((MOD(ROW(EK5:EK60),5)=0)*(EK5:EK60=2),EK6:EK61)

EDITED TO INCLUDE THESE ADDITIONAL COMMENTS:
While playing around some more, I came up with another (though, not necessarily better) regular-formula alternative:
=SUM(SUMIF(OFFSET(E5,{0,5,10,15,20,25,30,35,40,45,50,55},0),2,OFFSET(E6,{0,5,10,15,20,25,30,35,40,45,50,55},0)))
Note that there are a couple concerns to be aware of with that formula
• the OFFSET function is volatile, so it recalculates whenever any cell in the workbook recalcs
• Due to the hard-coding of offset values, it's only practical if there are a relative few referenced ranges

Is that something you can work with?

[jblaz]

That did the trick. Thank you sir.