Subtotal by color

Subtotal by color

Generic formula 

=SUMIF(color_range,criteria,number_range)

Related formulas 

Sum if multiple criteria

Subtotal invoices by age

Explanation

If you need to subtotal numbers by color, you can easily do so with the SUMIF function.

In the example shown, the formula in G5 is:

=SUMIF($B$4:$B$11,F5,$D$4:$D$11)

How this formula works

The SUMIF function takes three arguments: range, criteria, and sum_range. In this case, we are using:

Range: $B$4:$B$11 - This is the set of cells to which the criteria (a color from column F in this case) will be applied. This is an absolute reference that won't change when the formula is copied down. 

Criteria: F5 - a relative address that will changed when copied down. This reference simply picks up the criteria from the adjacent cell in column F.

Sum_range: $D$4:$D$11 - This is the set of cells being summed by SUMIF, when the supplied criteria is TRUE. This is an absolute reference that won't change when the formula is copied down.

Subtotal by color

Generic formula 

=SUMIF(color_range,criteria,number_range)

Related formulas 

Sum if multiple criteria

Subtotal invoices by age

Explanation

If you need to subtotal numbers by color, you can easily do so with the SUMIF function.

In the example shown, the formula in G5 is:

=SUMIF($B$4:$B$11,F5,$D$4:$D$11)

How this formula works

The SUMIF function takes three arguments: range, criteria, and sum_range. In this case, we are using:

Range: $B$4:$B$11 - This is the set of cells to which the criteria (a color from column F in this case) will be applied. This is an absolute reference that won't change when the formula is copied down. 

Criteria: F5 - a relative address that will changed when copied down. This reference simply picks up the criteria from the adjacent cell in column F.

Sum_range: $D$4:$D$11 - This is the set of cells being summed by SUMIF, when the supplied criteria is TRUE. This is an absolute reference that won't change when the formula is copied down.