This tutorial shows how to Subtotal by invoice number in Excel using the example below;
To subtotal values by invoice number, you can use a formula based on COUNTIF and SUMIF. In the example shown, the formula in E5 is:
How this formula works
This formula uses COUNTIF with an expanding range to first check if the current row is the first occurrence of a given invoice number:
This expression only returns TRUE when this is the first occurrence of a given invoice number. If so, a SUMIF calculation is run:
Here, SUMIF is used used to generate a total sum by invoice number, using the amounts in column D.
If the the count is not 1, the formula simply returns an empty string (“”)