Search code examples
excelsubtotal

Excel subtotal where the reference only shown once

does someone know How I can make the Excel to provide the subtotal based on the Company?

When I try the standard subtotal then it does not give me the right subtotal.

enter image description here

Solution

  • This is what you need to do to make the SUBTOTAL feature of Excel to work:

    enter image description here

    To use the Subtotal feature of Excel you cannot have blanks for the Company Column. You should have got an error message stating :

    Microsoft Excel cannot determine which row in your list or selection contains column labels, which are required for this command.

    • If you want the first row of the selection or list used as labels and not as data, click 0K.
    • If you selected a subset of cells in error, select a single cell, and try the command again.
    • To create column labels, click Cancel, and enter a text label at the top of each column of data.
    • For information about creating labels that are easy to detect, click Help.

    Not reinventing the process to fill down from above, you can refer this answer* on how to do it and then use the Subtotal feature.

    To outline the process, I did (Just in case one cannot follow .gif)

    • Select the Company range till the last row --> hit ALT+H+FD+S to open the Go To Special or can follow the answer*. Click Blanks --> enter = --> click ▲ and hit CTRL+ENTER together.
    • Value paste the range as well.
    • Hit CTRL+* to select all the data
    • Hit ALT+A+B to open Subtotal (will again get an error pop because there are blanks --> nevermind select OK and move ahead)
    • Ensure --> At each change in: --> Company --> Use Function: --> SUM --> Add Subtotal to: --> Check Value --> select in below only Value --> Hit OK (Selections are as per one's preference and requirements).
    • Now, hit CTRL+SHIFT+L to apply filter. Select the Invoice Column and filter out blanks --> Select Company Col --> hit ALT+; to select visible cells and press DEL from your keyboard --> remove filters to get what you need.

    May be not elegant or neat, but you could achieve this using Excel Formulas assuming no Excel Constraints and using MS365 then:

    enter image description here

    • Formula used in cell F2

    =LET(
     _Data, A2:D10,
     _Company, SCAN(,TAKE(_Data,,1),LAMBDA(x,y,IF(y="",x,y))),
     _Value, TAKE(_Data,,-1),
     _Sum, BYROW(_Company,LAMBDA(x, SUM((x=_Company)*_Value))),
     HSTACK(DROP(_Data,,-1),IF(_Value<>"",_Value,DROP(VSTACK("",_Sum),-1))))
    • Using LET() function makes easier to read, eliminates redundant ranges, increases performance.
    • _Data variable is the source range defined.
    • _Company variable where SCAN() and TAKE() function is used. Using the TAKE() function grabbing the Company range. Next, using SCAN() filling all the blanks from above value. Here, the initial value is an empty string which is the [initial_value] while the one returned using TAKE() is the range which needs to be filled in, it uses a LAMBDA() helper function, where x is the [accumulator] and y is the current range. If the current cell is empty it uses x while if not empty then retains the y, keeps iterating until and unless its filled up.
    • _Value variable is the value columns grabbed using TAKE() function.
    • _Sum variable uses BYROW() function, which applies a custom LAMBDA() calculations to each row in the array and returns one output per row.
    • Finally, in the last step, we are doing some logical test to return values when its TRUE and FALSE respectively. DROP() function used twice, which drops the last cell from the array. While HSTACK() function is used to combine the arrays into one and stack horizontally, to return the final output.

    Note: The zeros are hidden using Custom formatting --> General;;