Subtotal range above blank rows, subtotal in the last row adjacent right column

Adding the subtotals in an adjacent column is easy and requires no VBA. In C2 put this formula and copy down:

=IF(COUNTIF($A$2:$A2, $A2)=COUNTIF($A:$A, $A2), SUMIF($A:$A, $A2, $B:$B), "")


This isn't the programming forum, so VBA isn't the normal solutions offered here. What is the purpose of the blank rows? Truly needed? These subtotals are plenty obvious once added with the formula above.

Copy the formula from the post above, it should not be putting zeros in blank cells, that formula should be blank in those cells.

If you continue to have the zeros, post the workbook and I'll see what the problem is.

Still struggling with this. Any help is welcome. I cannot post the workbook due to the nature of the info.

---------- Post added at 03:44 PM ---------- Previous post was at 03:05 PM ----------

Here is a sample workbook of the desired effect, before and after.

Please Login or Register  to view this content.

Example of data with desired effects. (Column Headings are the same)

Please Login or Register  to view this content.

This is the exact same formula, just summing column G instead of column B, and it does not put zeros in... put this H2, then copy down:

=IF(COUNTIF($A$2:$A2,$A2)=COUNTIF($A:$A,$A2), SUMIF($A:$A, $A2, $G:$G), "")


If your column A is going to have blanks here and there, THAT would be a problem, so use this in that case:

=IF($A2="", "", IF(COUNTIF($A$2:$A2,$A2)=COUNTIF($A:$A,$A2), SUMIF($A:$A, $A2,$G:$G ), ""))

Well, there's no formula that can insert rows. That's a manual process, or a VBA project for the programming forum.

Please Login or Register  to view this content.