LoveByPDF logo

LoveByPDF
EN

Combined · COUNT / SUM per block

Put a total in every blank row of a column (sized to the rows above)

When a column has blocks of data separated by blank rows, each blank row can get its own SUM / COUNT over just the block above it.

A11 =COUNT(A2:A10)   A17 =COUNT(A12:A16)   A25 =COUNT(A18:A24)
Write it for my sheet

Examples

Type this in the Formula StudioFormula
“count from A2 to A10 and add this formula everywhere blanks are there in A column and adjust according to upper table”=COUNT(A2:A10)
“sum in all blank cells of column A”=SUM(A2:A10)

Tips

  • The studio previews every blank cell with its own range before anything is written, and one undo removes them all.

People also search for

  • sum in blank cells excel
  • subtotal in blank rows excel
  • insert total after each group in excel
  • sum above blank cell excel
  • auto sum blank rows excel
  • fill blank cells with formula excel
  • excel subtotal formula
  • count each block in excel
  • sum between blank rows in excel

FAQ

Is there a built-in way to add totals per group?

Yes, Data > Subtotal inserts a SUBTOTAL row at each change in a sorted column. It adds its own rows rather than filling your existing blank rows.

Why use SUBTOTAL instead of SUM for group totals?

SUBTOTAL ignores other SUBTOTAL cells in its range, so a grand total =SUBTOTAL(9,D2:D100) does not double-count group totals.

Can LoveByPDF write this formula for me?

Yes. Open your Excel file in the Excel Agent's Formula Studio, click a cell and type what you need - e.g. "count from A2 to A10 and add this formula everywhere blanks are there in A column and adjust according to upper table" - you get the formula, a preview on your sheet and an explanation, then insert it.

Related formulas