SOLVED

Subtotal function with Text Criterias

%3CLINGO-SUB%20id%3D%22lingo-sub-1996457%22%20slang%3D%22en-US%22%3ESubtotal%20function%20with%20Text%20Criterias%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1996457%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20do%20a%20subtotal%20of%20the%20number%20with%20multiple%20criteria.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EE.g.%20with%20the%20sample%20data%20below%2C%20there%20will%20be%20filters%20applied%20to%20the%20table%2C%20say%20date%20filters.%3C%2FP%3E%3CP%3EI%20am%20looking%20for%20the%20Subtotal%20of%20Color%20%3D%20%22Red%22%2C%20Fruit%20%3D%20%22Apple%22%2C%20where%20I%20should%20get%20the%20answer%2018.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20idea%20how%20I%20can%20achieve%20this%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%2280%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EDate%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EColor%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3EFruit%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3ENumber%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3E1%2F1%2F2020%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3ERed%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3EApple%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3E7%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3E1%2F2%2F2020%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3ERed%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3EWatermelon%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3E10%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3E1%2F3%2F2020%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3ERed%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3EApple%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3E2%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3E1%2F4%2F2020%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3EGreen%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3EPear%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3E8%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3E1%2F5%2F2020%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3EYellow%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3EMango%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3E4%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3E1%2F6%2F2020%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3ERed%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3EWatermelon%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3E3%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3E1%2F7%2F2020%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3ERed%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3EApple%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3E9%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3E1%2F8%2F2020%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3EGreen%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3EApple%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3E1%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3E1%2F9%2F2020%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3EGreen%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3EPear%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3E3%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3E1%2F10%2F2020%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3ERed%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3EWatermelon%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3E4%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3E1%2F11%2F2020%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3EYellow%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3EMango%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3E2%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3E1%2F12%2F2020%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3EGreen%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3EApple%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%20height%3D%2230px%22%3E%3CP%3E3%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1996457%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1997004%22%20slang%3D%22en-US%22%3ERe%3A%20Subtotal%20function%20with%20Text%20Criterias%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1997004%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F885269%22%20target%3D%22_blank%22%3E%40zyc93%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDepends%20on%20what%20you'd%20like%20to%20achieve.%20If%20you%20add%20Total%20row%20to%20the%20table%20and%20would%20like%20to%20sum%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20337px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F241320i58762AAD5EC588AA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EExcel%20on%20AutoSum%20automatically%20adds%20proper%20formula%20which%20sums%20only%20filtered%20values.%3C%2FP%3E%0A%3CP%3EIf%20you'd%20like%20to%20sum%20outside%20the%20table%2C%20here%20are%20two%20options%20-%20would%20you%20like%20to%20sum%20filtered%20values%20only%20or%20all%20of%20them%20independently%20of%20filtering.%20In%20first%20case%20helper%20column%20will%20be%20needed%2C%20and%20in%20both%20cases%20SUMIFS()%20works.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2005811%22%20slang%3D%22en-US%22%3ERe%3A%20Subtotal%20function%20with%20Text%20Criterias%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2005811%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bthank%20you%20for%20your%20example.%20I%20would%20like%20to%20sum%20outside%20the%20table%2C%20and%20to%20sum%20filtered%20values%20only.%20However%2C%20I%20am%20not%20too%20sure%20how%20to%20integrate%20SUMIFS()%20with%20SUBTOTAL()%20function.%20How%20could%20I%20do%20that%3F%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3ESay%20for%20example%2C%20on%20another%20page%2C%20I%20have%20table%20headers%20for%20different%20fruits.%20When%20I%20adjust%20the%20slicer%2C%20I%20would%20like%20for%20the%20row%20below%20to%20calculate%20the%20sum%20of%20each%20category%20basis%20the%20filters%20used.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22zyc93_0-1608534808322.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F242010i6C0AD096D2CDECC8%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22zyc93_0-1608534808322.png%22%20alt%3D%22zyc93_0-1608534808322.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2010696%22%20slang%3D%22en-US%22%3ERe%3A%20Subtotal%20function%20with%20Text%20Criterias%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2010696%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F885269%22%20target%3D%22_blank%22%3E%40zyc93%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20so%20you%20need%20to%20add%20the%20helper%20column%20to%20the%20table%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DAGGREGATE(3%2C5%2C%5B%40Color%5D)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ewhich%20indicates%20if%20current%20row%20is%20filtered%20or%20not.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20that%20formula%20in%20L2%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMIFS(Table1%5B%5BNumber%5D%3A%5BNumber%5D%5D%2C%20Table1%5B%5BFruit%5D%3A%5BFruit%5D%5D%2CL%241%2C%20Table1%5B%5BHelper%5D%3A%5BHelper%5D%5D%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20it%20to%20the%20right.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20exclude%20Fruit%20filter%20the%20rest%20sum%20could%20be%20received%20by%20reference%20on%20table%20total%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20806px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F242406i56FC387D974601E9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I am trying to do a subtotal of the number with multiple criteria. 

E.g. with the sample data below, there will be filters applied to the table, say date filters.

I am looking for the Subtotal of Color = "Red", Fruit = "Apple", where I should get the answer 18. 

 

Any idea how I can achieve this? 

 

DateColorFruitNumber

1/1/2020

Red

Apple

7

1/2/2020

Red

Watermelon

10

1/3/2020

Red

Apple

2

1/4/2020

Green

Pear

8

1/5/2020

Yellow

Mango

4

1/6/2020

Red

Watermelon

3

1/7/2020

Red

Apple

9

1/8/2020

Green

Apple

1

1/9/2020

Green

Pear

3

1/10/2020

Red

Watermelon

4

1/11/2020

Yellow

Mango

2

1/12/2020

Green

Apple

3

 

5 Replies

@zyc93 

Depends on what you'd like to achieve. If you add Total row to the table and would like to sum here

image.png

Excel on AutoSum automatically adds proper formula which sums only filtered values.

If you'd like to sum outside the table, here are two options - would you like to sum filtered values only or all of them independently of filtering. In first case helper column will be needed, and in both cases SUMIFS() works.

Hi @Sergei Baklan thank you for your example. I would like to sum outside the table, and to sum filtered values only. However, I am not too sure how to integrate SUMIFS() with SUBTOTAL() function. How could I do that? 

Say for example, on another page, I have table headers for different fruits. When I adjust the slicer, I would like for the row below to calculate the sum of each category basis the filters used. 

zyc93_0-1608534808322.png

 

Best Response confirmed by zyc93 (Occasional Contributor)
Solution

@zyc93 

If so you need to add the helper column to the table like

=AGGREGATE(3,5,[@Color])

which indicates if current row is filtered or not.

 

With that formula in L2 is

=SUMIFS(Table1[[Number]:[Number]], Table1[[Fruit]:[Fruit]],L$1, Table1[[Helper]:[Helper]],1)

and drag it to the right.

 

If exclude Fruit filter the rest sum could be received by reference on table total

image.png

 

@Sergei Baklan huge thanks! I've been looking for this exact solution. Have a merry Christmas and new year. 

@zyc93 

Thank you and Happy Holidays!