Enter sub-values ​​for individual groups and a sub-value for one group in only one column, for other cells in this group, leave them blank. Today we will introduce you how we can reduce the value for groups and only record the sub-value in specific cells.

We can obtain subtotal through simple subtotal function or SUM function in simple cases, but according to different requirement, we need to implement different functions or combinations. Today we will provide a formula that includes three functions IF / COUNTIF / SUMIFS to solve this problem. Through demonstrating a simple example, we will introduce you to the syntax, arguments of these functions, and show you how the formula works step by step. After reading the article, you may wonder if there are some other ways to solve this problem.

How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column

Refer to the table above, we can see that T-shirts are classified by colors, and the quantities for the week are listed separately. Our expectation is to “get subtotals for each product and record them in the appropriate cell”, see the example below:

How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column

We want to save 1) the sub-values ​​in the cells, which are filled in with background light orange. 2) For other cells in this column should be kept blank. 3) By the way, we also want to enter only one formula in D2, then subtotal must also be properly calculated in D5 and D8, pulling down the handle to fill the other cells.

Can we make a formula that can come above the three conditions? Really yes.

In this example, with the help of the IF, COUNTIF and SUMIF functions, we can properly calculate the subtotal for each product by just one formula.

Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column

Step 1: Select B2: B10, then define a new name for this category in the Name box, for example ‘Product’.
Step 2: Select C2: C10 in the Name box, define a new name for this category, for example ‘Amount’.
Step 3: In D2, the formula = IF (COUNTIF ($ B $ 2: B2, B2) = 1, SUMIF ($ B $ 2: $ B $ 10, B2, $ C $ 2: $ C $ 10), ” “) enter the.
Step 5: Pull the handle down to fill the other cells.

Verify that the by-product values ​​have been calculated correctly for each product. In addition, subtotals are only recorded in the first line of each group, the others remain empty.

How does this work

After explaining each argument in the formula, we will now show you how the formula works with these arguments.

Refer to the above steps, the formula converts to the format below in the formula bar.

=IF(COUNTIF("T-shirt (Red)","T-shirt (Red)")=1,SUMIF({"T-shirt (Red)";"T-shirt (Red)";"T-shirt (Red)";"T-shirt (Blue)";"T-shirt (Blue)";"T-shirt (Blue)";"T-shirt (Black)";"T-shirt (Black)";"T-shirt (Black)"},"T-shirt (Red)",{100;150;200;160;170;180;150;200;220}),"")