lucasbp February 2016

Sum first three (or four) non blank cells in a row

I have a box office sheet. It's far more complex than this, but basically it is:

enter image description here

I want to sum only the first 3 days gross of each film (opening weekend), jumping the blank cells. To make it a little more complicated, I want to add the possibility for a 4 or 5-day opening weekend. There could be a column where I'd enter the number of days.

I'm only familiarized with basic functions and a few complex ones. Is it possible to keep it simple?

Answers


Jeeped February 2016

While I usually try to accomplish formula results without array formulas¹, I don't know any way to accomplish this without one.

In E4 as an array formula¹:

=SUM(INDEX(F4:L4, 0, MATCH(1,SIGN(LEN(F4:L4)),0)):
     INDEX(F4:L4, 0, MATCH(1,SIGN(LEN(F4:L4)),0)+D4-1))

Fill down as necessary. Some IFERROR functionality could wrap around that for circumstanses where the # of days and available columns are mismatched.

        sum_by_days


¹ Array formulas need to be finalized with Ctrl+Shift+Enter↵. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.

Post Status

Asked in February 2016
Viewed 2,945 times
Voted 10
Answered 1 times

Search




Leave an answer