Last week, I asked you how you’d sum every other value in a column. Suhel_M was the first to suggest the idea I had in mind - we both used a helper column. Many of you suggested an array. We’ll discuss both solutions, but first, let’s review the helper column solution, shown below. The formula in column D
returns 0 or 1. The formula in cell C10
sums the values in column B where the corresponding value in column C equals 1. This formula starts summing with the second value in the data range, not the first. You could easily change the outcome by specifying 0 in the criteria argument, “=0″, instead.
Helper columns are a personal preference; whether supporting users or creating custom applications, helper columns seem easier to manage and maintain. Several of you suggested arrays, which are a great tool in the right hands! Darryl~ was the first to offer an array solution
(I changed the 0 to 1 in the MOD() function.) Rudi-S’s array is a bit shorter
Remember to press [Shift]+[Ctrl]+Enter when using array formulas.
David.White suggested using ISEVEN() instead of MOD(), which is a good alternative to MOD() in both solutions, and one I hadn’t considered. Sk.tomar’s solution was close to the helper column that Suhel_M and I both like, but uses literal criteria values and a DSUM() function.
Thanks for another interesting challenge. You provided a number of interesting approaches.