If you are using a version of Excel without the UNIQUE Function, you can combine the INDEX Function and MATCH Function with a COUNTIF Function to create an array formula to produce a list of unique values from a range of cells: Subtotal Table by Category or Group – Without UNIQUE Function. Note that in Excel 365, the UNIQUE Function is not case sensitive, but in Google Sheets it is. This is a dynamic array function where the size of the results list does not need to be defined, and it will automatically shrink and grow as the input data values change. When this formula is entered, a list is automatically created below the cell to show all unique values found within Product Group. However, first, we added the UNIQUE Function to cell E3: =UNIQUE(B3:B11) This is the final SUMIFS FUNCTION: =SUMIFS(C3:C11,B3:B11,E3) We can use the UNIQUE Function and the SUMIFS Function to automatically subtotal the Number of Products by Product Group. If you don’t have access to this function skip to the next section. This function is available in newer version of Excel, Excel 365, or Google Sheets. This first example requires that you have access to the UNIQUE Function. This tutorial will demonstrate how to calculate subtotals by group using the SUMIFS Function in Excel and Google Sheets.
0 Comments
Leave a Reply. |