Exceljet

Quick, clean, and to the point

Subtotal by invoice number

Excel formula: Subtotal by invoice number
Generic formula 
=IF(COUNTIF(range,criteria)=1,SUMIF(range,criteria,sumrange,"")
Explanation 

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:

=IF(COUNTIF($B$5:B5,B5)=1,SUMIF($B:$B,B5,$D:$D),"")

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:

COUNTIF($B$5:B5,B5)=1

This expression only returns TRUE when this is the first occurrence of a given invoice number. If so, a SUMIF calculation is run:

SUMIF($B:$B,B5,$D:$D)

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 ("")

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.