Search code examples
excelworksheet-function

Sum every 50th row in a column where the column also contains text - Excel

I have a workbook full of invoices, formatted for printing, and I need to sum the Total Due for all 295 invoices. The first Total Due is in H22 with the next amount 50 cells below in H71 (not including H22.) The rest of Column H contains the individual amounts that compose the Total Due and various text. Like this: 

Charges
10
20
30
Amount Due:
$60.00

If it would be easier to sum the range that makes up the total, that works too.

Each range is 10 rows, H11:H20. Then 50 below that at H60:69.

Edit: Cell H22, not H32

Solution

  • Use this array formula:

    =SUM(IF(MOD(ROW($H$1:INDEX($H:$H,MATCH(1E+99,$H:$H)))-1,50)=21,$H$1:INDEX($H:$H,MATCH(1E+99,$H:$H))))

    Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when Exiting edit mode. If done correctly then Excel will put {} around the formula.