Using combination of SUMIF and SUBTOTAL

[ali_plus]

Hi all,

Long time lurker and first time poster because I always find my solution here. How ever I am trying to create a SUM/SUBTOTAL for my report so when I filter the results, the subtotal shows me only those results. this is my configuration.

ROWS	Column B	Column C	Column D	Column E	Column F	Column G
1 (FILTER)	STORE CODE	Brand	SUBCHAIN	CHAIN	SHOWROOM NAME	RESULTS $
2	1111111	A	MODERN	WALMART	WALMART NY	1,000
3	22222	A	MODERN	TESCO	TESCO XX	999
4	22224	B	LOCAL	WALMART	WALMART CA	545
5	444141	A	LOCAL	TESCO	TESCO YY	54,454
6	12321	C	LOCAL	7/11	7/11 XX	10,149
7	454545	B	LOCAL	AMAZON	AMAZON ONLINE	99,999

<tbody>
</tbody>

Now before I was using a simple formula to filter by results by brand using this formula.
=SUMIF($C$2:$C$33162,"BRAND B",G2:G33162)

Now I want to use sumif and subtotal on my filter on the column so for example if I need to filter the results for "MODERN" Subchain and "TESCO" Stores only. I tried different combinations of sumif and subtotal but I think I need to use SUMPRODUCT formula. Can anyone help me to resolve my issue, or even understand it sorry English is not my native language.

 

[MARZIOTULLIO]

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	STORE CODE	Brand	SUBCHAIN	CHAIN	SHOWROOM NAME	RESULTS $
2​	1111111​	A	MODERN	WALMART	WALMART NY	1,000​		999​	999​
3​	22222​	A	MODERN	TESCO	TESCO XX	999​
4​	22224​	B	LOCAL	WALMART	WALMART CA	545​
5​	444141​	A	LOCAL	TESCO	TESCO YY	54,454​
6​	12321​	C	LOCAL	11-Jul​	7/11 XX	10,149​
7​	454545​	B	LOCAL	AMAZON	AMAZON ONLINE	99,999​

<tbody>
</tbody>

H2=SUMIFS(F2:F7,C2:C7,C2,D2:D7,D3)

or

i2=SUMPRODUCT((C2:C7=C3)*(D2:D7=D3),F2:F7)

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(SUBTOTAL(9,OFFSET($G$2,ROW($G$2:$G$7)-ROW($G$2),0,1)),--($D$2:D$7="MODERN"),--($E$2:$E$7="TESCO"))

Adjust the ranges to suit. Note that TESCO is tested for the CHAIN range.

 

[ali_plus]

Hi MARZIOTULLIO

Sorry I was not able to clear my question before. I am using the formula on top of the filters. Here is a screenshot for help.

Here I want to filter my results by TESCO stores in BRAND A. Is there a formula I can put on top by brands so it would be easy for me to filter the results? I can attach the link to file if you would like to have a look

https://www.dropbox.com/s/7ij0wic92ri47re/111SCREENNN.JPG?dl=0

 

[Aladin Akyurek]

See post #3...

 

[ali_plus]

See post #3...

Hi Aladin,

Thank You so much for the answer. However I needed one filter instead of two so I removed one

Cheers for the help.

This is the actual formula I used.

=SUMPRODUCT(SUBTOTAL(9,OFFSET(BD10,ROW(BD10:BD1333)-ROW(BD10),0))*($C$10:$C$1333="BRAND A"))

 

[Aladin Akyurek]

Hi Aladin,

Thank You so much for the answer. However I needed one filter instead of two so I removed one

Cheers for the help.

You are welcome.

This is the actual formula I used.

=SUMPRODUCT(SUBTOTAL(9,OFFSET(BD10,ROW(BD10:BD1333)-ROW(BD10),0))*($C$10:$C$1333="BRAND A"))

Try to run SUMPRODUCT in its native form (syntax) though...

=SUMPRODUCT(SUBTOTAL(9,OFFSET(BD10,ROW(BD10:BD1333)-ROW(BD10),0)),--($C$10:$C$1333="BRAND A"))

 

[ali_plus]

You are welcome.

Try to run SUMPRODUCT in its native form (syntax) though...

=SUMPRODUCT(SUBTOTAL(9,OFFSET(BD10,ROW(BD10:BD1333)-ROW(BD10),0)),--($C$10:$C$1333="BRAND A"))

Thank You Aladin. But can you explain the difference between two as I am still learning this formula?

 

[Aladin Akyurek]

Thank You Aladin. But can you explain the difference between two as I am still learning this formula?

=SUMPRODUCT(A2:A10*(B2:B10="jon"))

will fail on any text or blank in the sum range, a qualification that surfaces up often, in particular when A2:A10 contains formulas. SUMPRODUCT is insensitive to text values just like SUM, a behavior that allows for coherence in Excel as a computational system.

For your information...

=SUMPRODUCT(A2:A10*(B2:B10="jon"))

is slower than:

=SUMPRODUCT(A2:A10,--(B2:B10="jon"))

By the way, the native syntax of this function is:

=SUMPRODUCT(NumRange1,NumRange2,NumRange3)