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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ROWS[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[TD]Column G[/TD]
[/TR]
[TR]
[TD="align: center"]1 (FILTER)[/TD]
[TD="align: center"]STORE CODE[/TD]
[TD="align: center"]Brand[/TD]
[TD="align: center"]SUBCHAIN[/TD]
[TD="align: center"]CHAIN[/TD]
[TD="align: center"]SHOWROOM NAME[/TD]
[TD="align: center"]RESULTS $[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1111111[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]MODERN[/TD]
[TD="align: center"]WALMART[/TD]
[TD="align: center"]WALMART NY[/TD]
[TD="align: right"]1,000[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]22222[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]MODERN[/TD]
[TD="align: center"]TESCO[/TD]
[TD="align: center"]TESCO XX[/TD]
[TD="align: right"]999[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]22224[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]LOCAL[/TD]
[TD="align: center"]WALMART[/TD]
[TD="align: center"]WALMART CA[/TD]
[TD="align: right"]545[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]444141[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]LOCAL[/TD]
[TD="align: center"]TESCO[/TD]
[TD="align: center"]TESCO YY[/TD]
[TD="align: right"]54,454[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]12321[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]LOCAL[/TD]
[TD="align: center"]7/11[/TD]
[TD="align: center"]7/11 XX[/TD]
[TD="align: right"]10,149[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]454545[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]LOCAL[/TD]
[TD="align: center"]AMAZON[/TD]
[TD="align: center"]AMAZON ONLINE[/TD]
[TD="align: right"]99,999[/TD]
[/TR]
</tbody>[/TABLE]

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]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]STORE CODE[/TD]
[TD]Brand[/TD]
[TD]SUBCHAIN[/TD]
[TD]CHAIN[/TD]
[TD]SHOWROOM NAME[/TD]
[TD]RESULTS $[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

1111111​

[/TD]
[TD]A[/TD]
[TD]MODERN[/TD]
[TD]WALMART[/TD]
[TD]WALMART NY[/TD]
[TD]

1,000​

[/TD]
[TD][/TD]
[TD]

999​

[/TD]
[TD]

999​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

22222​

[/TD]
[TD]A[/TD]
[TD]MODERN[/TD]
[TD]TESCO[/TD]
[TD]TESCO XX[/TD]
[TD]

999​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

22224​

[/TD]
[TD]B[/TD]
[TD]LOCAL[/TD]
[TD]WALMART[/TD]
[TD]WALMART CA[/TD]
[TD]

545​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

444141​

[/TD]
[TD]A[/TD]
[TD]LOCAL[/TD]
[TD]TESCO[/TD]
[TD]TESCO YY[/TD]
[TD]

54,454​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

12321​

[/TD]
[TD]C[/TD]
[TD]LOCAL[/TD]
[TD]

11-Jul​

[/TD]
[TD]7/11 XX[/TD]
[TD]

10,149​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

454545​

[/TD]
[TD]B[/TD]
[TD]LOCAL[/TD]
[TD]AMAZON[/TD]
[TD]AMAZON ONLINE[/TD]
[TD]

99,999​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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)