Combine SUMIF and SUBTOTAL

[nparsons75]

Hi,

I have a table of data with a 3 cells that calculate the sum of a column (C) depending on the value in the adjacent column (B). So, SUM all values that contain the correct criteria. I have 3 criteria options, r,a,g. Basically red amber green. The formula works fine and shows what I need. However, the next step which I need to work I cannot get to grips with. I would like to filter using the standard table dropdown and select a customer. The result would then show me the SUM only for that specific customer. To do this I think! I need to somehow combine SUMIF and SUBTOTAL. Please see the table below, hopefully this helps to explain. Appreciate any help...

Book1
	A	B	C	D	E	F	G	H
3					Sum	Sum	Sum
4					670025	601219	424498	1695742
5			1695742
6	Customer	Risk	Value
7	David	r	319844
8	David	r	164511
9	David	g	251458
10	David	g	52320
11	Mark	a	104860
12	Mark	a	174600
13	Mark	r	97800
14	Mark	a	119000
15	Mark	a	99504
16	Steven	g	28320
17	Steven	g	92400
18	Charlie	a	102000
19	Charlie	a	1255
20	Charlie	r	75865
21	Charlie	r	12005
Sheet1

Cell Formulas
Range	Formula
E4	E4	=SUMIFS(C7:C21,B7:B21,"r")
F4	F4	=SUMIFS(C7:C21,B7:B21,"a")
G4	G4	=SUMIFS(C7:C21,B7:B21,"g")
H4	H4	=SUM(E4:G4)
C5	C5	=SUM(C7:C21)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B7:B21	Cell Value	="g"	text	NO
B7:B21	Cell Value	="a"	text	NO
B7:B21	Cell Value	="r"	text	NO

 

[jasonb75]

Here's one way using a helper column. I personally prefer this way but it can be done without the extra column if needed, it just makes the formulas more complex.
For reference the formula for "r" without the helper column would be =SUMPRODUCT(SUBTOTAL(9,OFFSET(C7,ROW(C7:C21)-ROW(C7),))*(B7:B21="r"))
Helper in column D with results in E5:H5. Original formulas left in place for reference.

Book1
	A	B	C	D	E	F	G	H
3					Sum	Sum	Sum
4					670025	601219	424498	1695742
5			1695742	595764	97800	497964	0	595764
6	Customer	Risk	Value	Value
11	Mark	a	104860	104860
12	Mark	a	174600	174600
13	Mark	r	97800	97800
14	Mark	a	119000	119000
15	Mark	a	99504	99504
Sheet1

Cell Formulas
Range	Formula
E4	E4	=SUMIFS(C7:C21,B7:B21,"r")
F4	F4	=SUMIFS(C7:C21,B7:B21,"a")
G4	G4	=SUMIFS(C7:C21,B7:B21,"g")
H4:H5	H4	=SUM(E4:G4)
C5:D5	C5	=SUM(C7:C21)
E5	E5	=SUMIFS(D7:D21,B7:B21,"r")
F5	F5	=SUMIFS(D7:D21,B7:B21,"a")
G5	G5	=SUMIFS(D7:D21,B7:B21,"g")
D11:D15	D11	=AGGREGATE(9,5,C11)

 

[fadee2]

You can add Customer name as additional Sumif Criteria. e.g.

E4	=SUMIFS(C7:C21,B7:B21,"r",A7:A21,D1)

This way you can get your results without using the filter dropdown.

Additionally you can use data validation in cell D1 to make it easier for users to select Customer Names.

Or Use Pivot table to achieve your goal.


hth....

 

[jasonb75]

@fadee2 The question asked for a way to get the desired results with the source data being filtered, which will require a formula that ignores hidden rows. SUMIFS doesn't ignore hidden rows.

 

[fadee2]

@jasonb75 I suggested a workaround, that is possible, since using sumif/pivot table, there is no need for the user to hide the rows, or use autofilter.

 

[nparsons75]

Here's one way using a helper column. I personally prefer this way but it can be done without the extra column if needed, it just makes the formulas more complex.
For reference the formula for "r" without the helper column would be =SUMPRODUCT(SUBTOTAL(9,OFFSET(C7,ROW(C7:C21)-ROW(C7),))*(B7:B21="r"))
Helper in column D with results in E5:H5. Original formulas left in place for reference.

Book1
	A	B	C	D	E	F	G	H
3					Sum	Sum	Sum
4					670025	601219	424498	1695742
5			1695742	595764	97800	497964	0	595764
6	Customer	Risk	Value	Value
11	Mark	a	104860	104860
12	Mark	a	174600	174600
13	Mark	r	97800	97800
14	Mark	a	119000	119000
15	Mark	a	99504	99504
Sheet1

Cell Formulas
Range	Formula
E4	E4	=SUMIFS(C7:C21,B7:B21,"r")
F4	F4	=SUMIFS(C7:C21,B7:B21,"a")
G4	G4	=SUMIFS(C7:C21,B7:B21,"g")
H4:H5	H4	=SUM(E4:G4)
C5:D5	C5	=SUM(C7:C21)
E5	E5	=SUMIFS(D7:D21,B7:B21,"r")
F5	F5	=SUMIFS(D7:D21,B7:B21,"a")
G5	G5	=SUMIFS(D7:D21,B7:B21,"g")
D11:D15	D11	=AGGREGATE(9,5,C11)

Thank you, this has worked perfectly....

I would also like to create a variation of the formula if possible?

=SUMPRODUCT(SUBTOTAL(9,OFFSET(C7,ROW(C7:C21)-ROW(C7),))*(B7:B21="r"))

Is it possible, where this is, "r" how could I replace this with something that instead of equalling r, replace with contains the text...."Car" for example...

 

[jasonb75]

For that you would need to perform a search on the criteria.

=SUMPRODUCT(SUBTOTAL(9,OFFSET(C7,ROW(C7:C21)-ROW(C7),))*ISNUMBER(SEARCH("car",B7:B21)))

 

[nparsons75]

Thank you Jason, both solutions worked perfectly.....